Make this formula more efficient? If statements/etc.

[trudiok]

Hello, and help!


This is the logic to follow. There are initial test scores and follow up test scores. Initial test scores count if they are the only test score. If there are both initial and follow up test scores, then the follow up test score should be chosen. Occasionally, there is a score and it is not known if this was an initial or follow up test. (unknown) In that case, if that score is higher, it should be chosen, but somehow marked/highlighted for confirmation of which type it is.

Two people came up with 2 formulas. The first one is short, sweet, but ignores the 'unknown' column and gives it a default value of "0".
The second one works the same, but does not ignore the unknown column and does seem to choose the correct response, but it is very long and I think it is doing the right thing, but since I can't really 'read the long formula' I am not sure!

Examples:

I-test = 50 F-test= 40 (choose 40)
I-test =50 F-test = blank (there was no F-test) Choose 50
I-test = 50 F-test=blank, U-test=60 Choose Highest (60), but review U-test to determine if should be initial test or follow up test. (this would have to be done individually, I think).


The formula being used in column F is as follows: =IF(OR(C2="",C2=0),B2,C2)
The one in Column G is: =IF(AND(C2<>0,NOT(ISBLANK(C2))),C2,IF(AND(C2=0,B2>=D2),B2,IF(AND(ISBLANK(C2),B2>=D2),B2, IF(AND(C2=0,B2< D2),D2, IF(AND(ISBLANK(C2),B2< D2),D2, IF(AND(ISBLANK(B2), ISBLANK(C2)), D2))))))

So, I am asking
1. Is there a better/shorter formula like the one used in Column G
2. Does the one that is there follow the logic and how can you add an element to highlight or somehow call attention to one that is based on the highest unknown test type?

Sorry if my explanations are lacking.

[dflak]

It would help a lot if you include a sample spreadsheet so we don't have to create one from scratch.

[trudiok]

Ok, see below?

[Beamernsw]

For that "Max Value" column, if that is supposed to show the largest score, then:-
In E2: =MAX(B2:D2)
For F2: =IF(C2>0,C2,B2)
For G2: =IF(D2>0,MAX(B2:D2),0)

I also added a Combined column. It includes both formulas in the one. It replaces columns F & G.

Conditional formatting shows matches to the choice in the combined column....But it will highlite duplicate numbers, as in 2 tests both having the chosen score.

[Beamernsw]

I added another column to act as a written confirmation of choice.
Should be more accurate then the highlighting.

[trudiok]

Excel Testing Part 3.xlsx

Thanks so much Beamernsw

I don't know if I 'broke' the one you supplied or if it isn't working on some of them as expected. See the attachment.

(Also, I probably didn't explain it well enough, either)

[Beamernsw]

Ok, sent pm's....finally