Additional IF statement help

[slam]

I'm starting out with this pretty straight forward formula on a worksheet called R1 in cell I3 to I34 (I3 shown):


=IF(B3="","",IF(G3="DNF",G3,IF(G3="DSQ",G3,Setup!B12)))


The worksheet records a runners points for a race, and Setup!B12 is just a number (points for a race).


I need to add 3 additional conditions to this. I'll do my best to describe.


1. If the last else condition is met, for the quickest time in F3:F34 only, I also need the value from Setup!B9 added.
2. If the last else condition is met, for the quickest time in H3:H34 only, I also need the value from Setup!B10 added.
3. If the last else condition is met, and there is a 0 in column K, I also need the value from Setup!B11 added.


To be clear, only 1 person will have #1 applied to them, only 1 person will have #2 applied to them, but any number of people can have #3 applied to them. One person could also have all three at once.


Times for #1 & #2 are entered in the format mm:ss.000.

Here is an example worksheet: https://dl.dropboxusercontent.com/u/57757349/Spreadsheet Development/Example.xlsx


Any help would be greatly appreciated.

 

[MarcelBeug]

No need for additional IF statements. Just add after "Setup!B12"

... +((F3=MIN($F$3:$F$34))*Setup!$B$9)+((H3=MIN($H$3:$H$34))*Setup!B$10+((K3=0)*Setup!$B$11) ...

Additionally I would combine the check against G3 in 1 statement

... IF(OR(G3="DNF";G3="DSQ"),G3 ...
or even 
... IF(G3<>""),G3 ...

Remark: I didn't test my codes, but they should be OK.

 

[MarcelBeug]

Oops, small correction (; to ,):

... IF(OR(G3="DNF",G3="DSQ"),G3 ...

 

[slam]

Perfect, thank you!

 

[slam]

Hi MarcelBeug - may I ask one additional request please?

Is it possible to have this formula ignore blanks? It seems to work as intended when there is one blank, but when all cells in column F or H are blank, it will add the value from Setup B11 and Setup B12. I don't want it to do that.

This is my current formula:

=IF(B3="","",IF(OR(G3="DNF",G3="DSQ"),G3,Setup!B14+((F3=MIN($F$3:$F$34))*Setup!$B$11)+((H3=MIN($H$3:$H$34))*Setup!B$12+((K3=0)*Setup!$B$13))))

Thank you

 

[MarcelBeug]

Try the following adjustment:

=IF(B3="","",IF(OR(G3="DNF",G3="DSQ"),G3,Setup!B14+([COLOR="#FF0000"](F3>0)*[/COLOR](F3=MIN($F$3:$F$34))*Setup!$B$11)+([COLOR="#FF0000"](H3>0)*[/COLOR](H3=MIN($H$3:$H$34))*Setup!B$12+((K3=0)*Setup!$B$13))))

 

[slam]

Thank you! Looks to be working!