Anyone here good at Excel? Conditional formatting help needed!!

Sky King

Hi all!

I am kinda new here, only one or tweo posts. I logged on months ago to help answer questions for someone who was interested in learning to fly. Now I am hoping that the karma will come full circle and I will get the help I need!!

Basically I am stuck with an important deadline looming and i need to write an excel conditional command (as outlined below). Now, I know that this is easily done in basic or whatever, but I am limited to working with excel. I'd be surprised if someone who is good at excel couldn't do this in a minute or to. I would really appreciate any pointers i could get - nobody I know who is anyway decent at excel can help me

So I need to do this:

I have an excel file generating a column of random numbers. No problems there. What i need is a command that will add up these numbers until they exceed a certain other number (eg 500). Then to stop and tell me how many random numbers it added. Check out this (albiet lame) pseudocode i wrote:

If(random#1) > 500 then "1"

if not then add (random#1 and random# 2)

If(random#1 + random# 2) > 500 then "2"

you dig?

fasty

Pulled right out of Excel help...

IF(logical_test, value_if_true, value_if_false)

Are random#1 and random#2 columns or what? If so, how many columns of random numbers will you have? Can you post an example of the data and how you want it to look?

Sky King

Hi fasty, and thanks for the reply. That last explanation was a bit rushed, I had to go to a lecture!

I have actually looked at the excel help file. i have used basic IF statements before, so I get the jist roughly.

Basically what i am doing is simulating distances tumbled by a particle in a rotating drum. I have all the stats done and know how the probability distribution is behaving and all that jazz OK!

So what i have now is a column in Excel of random numbers sampled from a log-normal distribution. These are in effect, simulated tumble distances.

I need to know how many times the particle tumbles before it leaves the drum. The drum is 500mm long, so i need to know how many of the numbers to add up to exceed 500, because when 500 is exceed, the particle leaves the drum!!.

So take this for example as a column of random numbers:

A1
30mm
-5mm
45mm
27mm
3mm
-10mm
19mm
31mm
50mm
22mm


I need excel to keep adding these numbers until 500mm is exceeded, at which point it will tell me how many numbers it had to add.

This is the number of tumbles the particle took before it left the drum [N]. I need excel to save N in another cell so that I can record it, and then run the simulation again.

Obviously every time a set of numbers is randomly generated, [N] will be different. So I will run this simulation say 100 times and see what results i get.


am i making sense at all??

FSL

You could do it using a macro. Six lines of simple code is all you would require to place the answer in a fixed cell.

Alternatively you could do it with a arithmetic formula in one column, the conditional statement in another and a constant in a third. The formula and the conditional statement would be in row 2 and copied down as far as your random list extended. the constant cell would be dragged down as a series. The if true part of the conditional statement would place the answer in a fixed cell.

That should be enough to get you thinking along the correct lines. A final hint for the macro if the answer exceeds the number of tumbles in your series the item is still in the drum. Similarly for the alternative if the answer cell was empty the item would still be in the drum.

dewsbury

Hi Kilkenny flyer,

It strikes me that you need precise instructions rather than a broad outline.

I cannot give precise instructions but can direct you elsewhere.

Try www.mrexcel.com or www.exceltip.com.

These are US sites (more worldwide really) and there are loads of people online that will help. (Many more people than on boards).
I have used it before and got great results.

Ensure that you subject line is detailed and meaningful. Also keep your query reasonably short but precise.

FSL

Hi dewsbury

The purpose of the post was to get him thinking not do his homework for him. It would have taken me less time to write the macro or post the appropriate formulae

Sky King

Thanks for the replies guys (&girls?).

My main problem here is lack of time. I am sure I would have no problem figuring this out if I had the luxury of more of it, but I don't. My own problem perhaps, but I didn't envisage it. Such is the nature of research, no?

Anyway, the main point is that you folks reckon this could be done... (and quite easily according to FSL!). As regards the homework.. this stuff is statisticsal and mathematical theory. I am not going to get extrra kudos for having an exel macro that does it =, it just makes life a hell of a lot easier for me rather than working all these out manually (which i will have to do in the event I don't get it working).

Hmm, I'll give a read over this thread again early tomorrow (when i am not half asleep) and have a poke around at excel again and see what I come up with.Cheers.

Seth Brundle

Thought will have to be given to the fact that you are using strings:

30mm
-5mm
45mm
27mm
3mm
-10mm
19mm
31mm
50mm
22mm

and not integers

30
-5
45
27
3
-10
19
31
50
22

edit: unless the random numbers don't have "mm" concatenated on the end!

FSL

O.k here is a macro which will put the number of tumbles into cell b1 if column A contains the numbers without the mm. If it is not possible to exclude the mmm then I can change the macro to strip of the mm

Sub Checkvalues()

x = 0
i = 1
Do While Cells(i, 1) <> ""
x = x + Cells(i, 1)
i = i + 1
If x > 500 Then Exit Do
Loop
Cells(1, 2) = i - 1
End Sub

Sky King

Yesssssssssssssss!
LOVE that feeling

FSL I extend to you my sincerest gratitude. You have saved me countless hours mid-night work with your six lines of simple code at a time when I am going grey from stress!

I guess karma does exist. I uploaded screenshots of the masterpiece in action if you are interested. (You may not be).

http://i6.photobucket.com/albums/y229/dieseltrout/montecarlo1.jpg
http://i6.photobucket.com/albums/y229/dieseltrout/montecarlo2-1.jpg

I have never used VB before so that took a little poking around before I got it working, but get it working I did, and seeing those tumble numbers come popping up brings me serious satisfaction!

The bit highlighted in luminous green is the simulated drum residence time. Basically i take repeats of this and compare it to the experimental data to see if my statistical model is accurate.

Thanks again
J