Jump to content

Photo

Excel Query


  • Please log in to reply
7 replies to this topic

#1 Calvary

Calvary

    Conceptual

  • Members
  • 6,624 posts
  • Locationwww.

Posted 09 June 2016 - 12:52 AM

Hi all,

 

There have been a couple of times where I've wanted to use the IF statement in excel but find it doesn't quite fulfill what I need it to do. One specific simple example is this:

 

Say you wanted to programme a simple coin toss...

 

e3ad06cd8f6757d6c2d3334b9c849c33.png

 

If you input "Y" into C7, the output will either be "H" for heads, or "T" for tails.

 

What I want to do is this:

 

=IF(C7="Y","H"OR"T"," ")

 

Basically I need something to facilitate the function of OR here, so that I can have more than one variable returned if the input in C7 is...inputted.

 

Does this make sense?

 

Idk, help someone pleeease :(

 


tumblr_om7nwjm5Wm1rsea1wo1_500.gif
Ask for my discord/Insta/Tumblr if you want.


#2 fae

fae

    Terabyte

  • Members
  • 1,329 posts
  • LocationOver the hills, where the seven dwarfs dwell

Posted 09 June 2016 - 01:12 AM

So you randomly want to either get 0 or 1 as your return value if C7 is Y.

I guess you can use the rand() function, it returns a random number between 0 and 1

 

If (C7 = "Y", If (rand() > 0.5, "H", "T"), " " )

 

something like this should work


Et j'aime la nuit écouter les étoiles. C'est comme cinq cent millions de grelots. - Antoine de Saint-Exupéry


#3 Calvary

Calvary

    Conceptual

  • Members
  • 6,624 posts
  • Locationwww.

Posted 09 June 2016 - 01:26 AM

Ha! It works, didn't now you could use the Rand function like that lol!

tumblr_om7nwjm5Wm1rsea1wo1_500.gif
Ask for my discord/Insta/Tumblr if you want.


#4 fae

fae

    Terabyte

  • Members
  • 1,329 posts
  • LocationOver the hills, where the seven dwarfs dwell

Posted 09 June 2016 - 01:29 AM

it's not very nice because you can argue that the distribution is not exactly 50/50 but should be fine I think.

The problem with your request is that you need some kind of randomization so using the rand() function is just the quick and dirty solution ;)


Et j'aime la nuit écouter les étoiles. C'est comme cinq cent millions de grelots. - Antoine de Saint-Exupéry


#5 Calvary

Calvary

    Conceptual

  • Members
  • 6,624 posts
  • Locationwww.

Posted 09 June 2016 - 03:06 AM

It does the job I suppose, close enough haha.

I was doing die rolls as well just out of boredom, and I realised that the code for that would be slightly more complex than I initially realised. Die rolls are of course random, but statistically you're more likely to get a middle number if you increase the number of rolls, I.e. if you roll 3d6 you're more likely to get a 12 than a 4. Therefore graphically you'd see a bell curve, which wasn't happening when I used 'randbetween(3,18)'...you have to do a randbetween for each individual die roll and add them up to get an accurate dice roll representation. Idk, fun boring fact for you haha.

tumblr_om7nwjm5Wm1rsea1wo1_500.gif
Ask for my discord/Insta/Tumblr if you want.


#6 fae

fae

    Terabyte

  • Members
  • 1,329 posts
  • LocationOver the hills, where the seven dwarfs dwell

Posted 09 June 2016 - 03:15 AM

Why don't you code a function for one dice and run it 3 times and then add up the results?


Et j'aime la nuit écouter les étoiles. C'est comme cinq cent millions de grelots. - Antoine de Saint-Exupéry


#7 Calvary

Calvary

    Conceptual

  • Members
  • 6,624 posts
  • Locationwww.

Posted 09 June 2016 - 05:41 AM

Could have, but this way at least most of the work is done for you, although the equation looks like a mess because there are 10^26 brackets

tumblr_om7nwjm5Wm1rsea1wo1_500.gif
Ask for my discord/Insta/Tumblr if you want.


#8 fae

fae

    Terabyte

  • Members
  • 1,329 posts
  • LocationOver the hills, where the seven dwarfs dwell

Posted 09 June 2016 - 08:28 AM

okay  :D


Et j'aime la nuit écouter les étoiles. C'est comme cinq cent millions de grelots. - Antoine de Saint-Exupéry