Refinement to Condition Detection routine 1

[MeGustaXL]

Hi All,
I started with a simple row of data like this:

	A		B	C			D
1	Engine	30	=M*(-LN(RAND()))	=IF(C1<B1,1,0)

I have a looping VBA routine which adds the values in column C to a running total variable ‘Total_T’ when there is a ‘1’ in column D.

If the table expands like this:

	A		B	C			D
1	Engine	30	=M*(-LN(RAND()))	=IF(C1<B1,1,0)
2	Gearbox	300	=M*(-LN(RAND()))	=IF(C2<B2,1,0)
3	Suspension	500	=M*(-LN(RAND()))	=IF(C3<B3,1,0)
4	Steering	500	=M*(-LN(RAND()))	=IF(C4<B4,1,0)
5	Brakes	120	=M*(-LN(RAND()))	=IF(C5<B5,1,0)

How do I code the thing to detect each occurrence of a ‘1’ in column D, and add the corresponding value from column C to ‘Total_T’

At the moment, I’m detecting the presence of a ‘1’ with the array formula “{=IF(D15=1,1,0)}” which gives a ‘1’ if any of the cells is equal to 1.


And P.S. How do I put tables like this in my post so they line up nicely?


Chris

Varium et mutabile semper Excel

http://www.insys-ltd.co.uk

 

[comaboy]

Chris,

Maybe I missed the point, here, but the seemingly* obvious solution would be cell e1 =d1*c1. Since 0 * Anything = 0, and 1 * Anything = Anything you are left with the numbers you want to sum. Fill down and auto-sum. Job done!



*seemingly = I probably read the post wrong!

Comaboy

 

[MeGustaXL]

Thanks Comaboy,

You're absolutely right, of course, and it's me that's in a coma! Have a STAR just for being sentient

I didn't fully explain what I want to do, however.

Having detected 1's against, say the Engine and Steering, I need to report elsewhere that they have failed at for example, 29.8 and 158.6 respectively. I'm after a kind of Leftwards-looking conditional Lookup...

Wait a minute - there's something about that in FAQs; I'll have a dredge and see what's there.


Chris

Varium et mutabile semper Excel

http://www.insys-ltd.co.uk

 

[comaboy]

LOL! It's usually ME not seeing the wood for the trees! Great what I fresh pair of eyes can see, eh!

It *sounds* like you're after the VLookUp function, btw!

..or maybe a "back sheet" linked to the main data and sorted...