Create different formulas based off of "Yes" or "No"

[fordieuk]

Guys,

I have 35 columns all with either "yes" or "no" drop down list options, if a "yes" is picked I want it to calculate a formula i.e. A1*B1 but if a "no" is selected I want it to move onto the next "Yes" which will have a different formula i.e. D1*E1, this is repeated 35 times any idea how I can get this into one formula?

Any ideas?

[martindwilson]

a bit more info needed can they all be yes or no
if it's say
y
n
n
n
y
y
if any of the n's is picked they would go to the second last y

[fordieuk]

No it will stop once a yes has been hit.

[martindwilson]

sorry thts not clear whet should each of those n's look to do,also what happens if there isn't a y below them?

[martindwilson]

ok here is my first attempt

[fordieuk]

Thanks I think I can adapt that, just one thing can you adjust the formula so when a "n" is selected it will simply just display a "0" instead of searching for the next "y".

Hope that makes sense and thanks in advanced! True legend!

[Ace_XL]

Wouldn't that just equate to

=IF(A2="N",0,E2)

[martindwilson]

ah i just love unnecessary questions!

[fordieuk]

Apologies, it sort of works but what I am trying to do is just use one cell to display the "Y" formula.

For example on your spreadsheet B1 would be the cell that displayed the next Y formula found in the A column. So if we didnt have a Y until cell A5 then B1 would display 30, and if we didnt have a Y until A12 and all the previous A1,A2... were N then B1 would dispaly 156.

Does that make sense now?

thanks

[martindwilson]

so you will only have on yes in the whole lot then? oh and include your excel version in your profile

[fordieuk]

Please see attached, hope that makes sense?

[martindwilson]

ok this should do it

[Marcol]

Maybe this in D12 ...

Please Login or Register  to view this content.

[Marcol]

Or try this in D12 ...

Please Login or Register  to view this content.

Drag/Fill Down

This will multiply the preceding 2 values for each instance of "Yes", then sum them.

[fordieuk]

Martin that works a dream, one last thing how do I get the #N/A to return a 0 figure? #N/A obviously is returned when a Yes isnt in a sequence.

regards

[martindwilson]

see Marcol's post #13, mind you that's why i said you should include your excel version in your profile if you have excel>=2007
use
=iferror(INDEX(E12:Z12,MATCH("yes",E12:Z12,0)-1)*INDEX(E12:Z12,MATCH("yes",E12:Z12,0)-2),0)