#### slam

##### Well-known Member
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.

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Code:
``... +((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
Code:
``````... IF(OR(G3="DNF";G3="DSQ"),G3 ...
or even
... IF(G3<>""),G3 ...``````

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

Oops, small correction (; to ,):

Code:
``... IF(OR(G3="DNF",G3="DSQ"),G3 ...``

Perfect, thank you!

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:

Code:
``=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

Code:
``=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))))``

Last edited:
Thank you! Looks to be working!

Replies
0
Views
206
Replies
1
Views
322
Replies
17
Views
382
Replies
2
Views
671
Replies
1
Views
551

1,207,089
Messages
6,076,518
Members
446,211
Latest member
b306750

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back