Calculate ISBLANK with multiple conditions

In the attached WB... I have a formula in DATA T2 (cited below, from a different spreadsheet) that conditions where a cell is blank or not. I would like to get the blended result of (EXIT 1 + EXIT 2) - (ENTRY + ADD 1 + ADD 2). The reason for the ISBLANK is no need to divide by X if the cell is blank. Anyhow, I just cannot figure out how to add multiple ISBLANK into the formula.

Please Login or Register  to view this content.

It's not clear what you want to do.

The cell references in your post are different than what's in the file.

Formula:

Please Login or Register  to view this content.

"no need to divide by X if the cell is blank"
What is X? You are dividing by 2.

The logic in your current formula says:

If EXIT 1 is blank, then EXIT 2 - ENTRY
Else (EXIT 1 + EXIT 2)/2 - ENTRY

No idea how this relates to your requirement of
(EXIT 1 + EXIT 2) - (ENTRY + ADD 1 + ADD 2)

Here's possibly a better attempt to explain...

The EXIT aspect won't change, but I need to (1) discover if ADD 1 and/or ADD 2 is not blank, then (2) possibly add to ENTRY, then (3) divide by that many.

So

If EXIT 1 is blank, then EXIT 2 - ENTRY
Else (EXIT 1 + EXIT 2)/2 - ENTRY

minus

ENTRY
+
If ADD 1 is not blank
+/or
If ADD 2 is not blank

Then add together and divide by that number
Else ...

I cannot simply divide by 3, because it will skew the result I'm looking for. I need to add those that are not blank together, then divide by that many.

This formula uses structured references instead of cell references, which work better in your table.

=IF(ISBLANK([@[EXIT 1]]),[@[EXIT 2]]-[@ENTRY],(([@[EXIT 1]]+[@[EXIT 2]])/2)-[@ENTRY])-([@ENTRY] +[@[ADD 1 ENTRY]]+[@[ADD 2 ENTRY]])/(1+ISBLANK([@[ADD 1 ENTRY]])+ISBLANK([@[ADD 2 ENTRY]]))

If we break it up it's easier to read:

Please Login or Register  to view this content.

If you use ISBLANK in an arithmetic expression, it will be 0 if FALSE and 1 if TRUE. So you can use it to determine if you need to divide by 1, 2, or 3 in the last part.

I've determined that this is not going to work. I've found another way, but requires additional input.

Here's the formula I ended up with for the POINTS column...

Please Login or Register  to view this content.

Here's the new thread...

https://www.excelforum.com/excel-for...ml#post5592466