Stuck on correct function(s) & formula!

On my spreadsheet tab 'Scores and Assigned Priorities', I have a Baseline score (column D) and a Next Assessment score (column E).

I need column G to:

1. know the difference if column D has a score and column E is empty
2. rate the score in column D when it's by itself AND (E minus D) only when both have a score
3. rate the D or (E-D) score as 1=high, 2=medium, 3=low, 4= NA, NA=NA

This is what I have so far, but it's not working when D is the only column with a score in it. I've tried a bunch of OR, AND functions, but that didn't help. I'm out of my league :D

=IF(SUM(E71-D71)<=0,"HIGH",IF(SUM(E71-D71)<=1,"MEDIUM",IF(SUM(E71-D71)=2,"LOW",IF(SUM(E71-D71)>=3,"NA"))))

Thanks for any assistance. The file is attached.
Catherine

It looks fine to me, can't see an issue. If it is an N/A issue just try - If(E71="", that should stop the error.

What is the result required when E="NA"?

Tried that. It still has the #value! error.

It gives the #value! error

=IF(E71="NA",IF(D71=1,"HIGH",IF(D71=2,"Medium",IF(D71=3,"Low","N/A"))),IF(E71-D71<=0,"High",IF(E71-D71=1,"Medium",IF(E71-D71=2,"Low",IF(E71-D71=3,"N/A")))))

Replace ????? with required outcome

=IF(E71="NA","?????",IF(E71-D71<=0,"HIGH",IF(E71-D71<=1,"MEDIUM",IF(E71-D71=2,"LOW",IF(E71-D71>=3,"NA")))))

=IF(OR(E71="NA",E71=""),IF(D71=1,"HIGH",IF(D71=2,"Medium",IF(D71=3,"Low","N/A"))),IF(E71-D71<=0,"High",IF(E71-D71=1,"Medium",IF(E71-D71=2,"Low",IF(E71-D71=3,"N/A")))))

This also covers you if it is empty.

Originally Posted by DannyJ

=IF(E71="NA",IF(D71=1,"HIGH",IF(D71=2,"Medium",IF(D71=3,"Low","N/A"))),IF(E71-D71<=0,"High",IF(E71-D71=1,"Medium",IF(E71-D71=2,"Low",IF(E71-D71=3,"N/A")))))

Ok, that helped, but, G is still showing "MEDIUM" if there is a number value in column D and NA in column E.

.... please answer ....

What is the result required when E="NA"?

=IF(OR(E71="NA",E71=""),IF(OR(D71="NA"),IF(D71=1,"HIGH",IF(D71=2,"MEDIUM",IF(D71=3,"LOW","NA"))),IF(E71-D71<=0,"HIGH",IF(E71-D71=1,"MEDIUM",IF(E71-D71=2,"LOW",IF(E71-D71=3,"NA"))))))


Almost there:

When E = "NA", G = "NA"
When D and E = "NA", G = "NA"

but

When E= "NA" and D = 1,2,3, G = #value!
When D= "NA" and D = 1,2,3, G = FALSE

I need for E to have "NA" result when
1. Both D and E are "NA"
2. Both D and E are "4"
3. E is "4"

I think that's it, maybe

Again, your explanation is not clear but I think I have what you wanted.

=IF(AND(E71="NA",D71="NA"),"N/A",IF(AND(D71=4,E71=4),"N/A",IF(E71=4,"N/A",IF(OR(E71="NA",E71=""),IF(D71=1,"High",IF(D71=2,"Medium",IF(D71=3,"Low"))),IF(E71-D71<=0,"High",IF(E71-D71=1,"Medium",IF(E71-D71=2,"Low",IF(E71-D71>=3,"NA","Error"))))))))

Great, still need a bit more.

How do I add (I hope this makes sense!):

IF(AND(D71="NA",E71="1,2,3,4"),"",(E71-D71=1,"HIGH",(E71-D71=2,"MEDIUM",(E71-D71=3,"LOW",(E71-D71=4,"NA")

Try

IF(AND(D71="NA",E71>=1,E7<=4),"",(E71-D71=1,"HIGH",(E71-D71=2,"MEDIUM",(E71-D71=3,"LOW",(E71-D71=4,"NA")

That one doesn't work; tried it by itself and in the larger formula. Any other ideas?

I suspect we (I am!) are confused now about what the conditions are:

Perhaps produce a table with ALL possible value in D & E and the required results in G.

I'm sorry if I'm not being clear

I can try to clarify, if it helps? Let me know.

Isn't there an easier way than nesting so many IF functions to account for all the possible outcomes? I had something with many possible outcomes before, and was advised to simplify it with a CHOOSE function (which I'm not very familiar with). But, in this case, I'm not sure what other functions could be used.

Conditions are: ????

Both D and E have a number < 4 ... take difference (D-E) ==> High, Medium, Low

D has a number, E is blank ... result ?

D= NA, E has a number < 4 (?) ... result ?

D = NA, E=4 ... result NA

D=NA, E=NA ... result NA

D=4, E=4 .. result NA

To avoid complex IF statement(s) I have used a table:

the table is a named range called "Grade_tbl" in columns E and F (of the attached)

Column E is a concatenation of D and E values: note that Blank E is an empty cell i.e. not a space.

Column F is the result

H., I and J are to test the results: VLOOKUP in J2.

=VLOOKUP(H2&$I$2,Grade_tbl,2,0)

That is a beautiful table

How do I insert the table into the G71 cell? (use a named range, and insert the table as an object into a cell? I don't know how to do that )

Or, do I have to take the table and stick it in a hidden range or sheet? (don't know how to do that either, if you can help?)

Copy the table into any range of cells (could be separate sheet).

Select the table

Go to Formulas.... Define Name .... and set the name of the table to "Grade_tbl" (or change if required)


In Column G:

insert the VLOOKUP formula in first cell and fill down, changing H2 & I2 to Dx and Ex (x is start row).

=VLOOKUP(H2&$I$2,Grade_tbl,2,0)

See the attached.

I noted D and E had 0 (zeros) which I deleted (assumed to be "blanks")

table in sheet "Tables"

Ok, it's working when both D and E have values. It is not working when E is blank, and I'm not sure why it's giving a blank E a value of 0

D E G

3 0 #N/A
3 0 #N/A
1 0 #N/A
1 2 MEDIUM
1 3 LOW
1 4 NA

You get 0 because the "Next Assessment" in (say) "Human Resources" is blank. You could change the table to reflect 0 rather than blank.

We just had some overlap of communication

I'd prefer that E remain "blank" until the Next Assessment criteria are completed.

I have added another helper cell in column G of the scores tab. Have a look and see if that works. It just uses the formula =IF($E70=""," ",$E70). Then change the reference of John's formula to G instead of E.

Originally Posted by DannyJ

I have added another helper cell in column G of the scores tab. Have a look and see if that works. It just uses the formula =IF($E70=""," ",$E70). Then change the reference of John's formula to G instead of E.

Thanks for the suggestion. I'd rather not add a column.

I'm wondering why I can't add what is needed to the VLOOKUP formula, if that's what it takes to get rid of the 0 when any cell in D or E is blank?

What harm does adding a column do? It can be hidden anyway.

What harm does adding a column do? It can be hidden anyway.

What harm does adding a column do? Just hide it.

I'd prefer that E remain "blank" until the Next Assessment criteria are completed.

Perhaps use


=IF('MANAGEMENT RESOURCES'!H11=0,"",'MANAGEMENT RESOURCES'!H11)

to change zeros to "blank"

That did the trick! Thank you!!

I think I'm almost done

Just trying to keep it super clean; I'm presenting this to international partners

I'll have to think about this: a table would be preferable but need to consider we are extracting cell value not fixed values

Try

=IF(OR(AND( 'VISION AND MISSION'!H11="",'VISION AND MISSION'!G11="NA"),AND('VISION AND MISSION'!H11="",'VISION AND MISSION'!G11=4), 'VISION AND MISSION'!H11=4),"NA",OFFSET('VISION AND MISSION'!$B11,,'VISION AND MISSION'!H11))

=IF(OR(AND( 'VISION AND MISSION'!H11="",'VISION AND MISSION'!G11="NA"),AND('VISION AND MISSION'!H11="",'VISION AND MISSION'!G11=4), 'VISION AND MISSION'!H11=4),"NA",OFFSET('VISION AND MISSION'!$C11,,'VISION AND MISSION'!H11))


Ok, I adjusted for the right criteria cell (C11): this formula works partially.

I'm heading home from work, and will detail it down in about an hour.

Since the original formula for column G worked so well, I was hoping it would be relatively easy to duplicate that success for H, but I didn't consider all the possible data combinations until we worked on the last tab

With C11 you will get D,E and F from the offsets not C, D, E.

This formula really isn't working in many ways:

1. I need 'Action Plan' to auto populate the criteria from 'Vision and Mission', based on the matching score value of the criteria 'Vision and Mission' G 11 (1,2,3) in 'Vision and Mission' C,D,E

2. I need it to do this when only the Baseline Assessment G is filled out, but Next Assessment H is blank

3. I need 'Action Plan' auto populated criteria to change to same rule for H (next assessment) when H is filled out, overriding the previous result of G alone.

This starting formula worked perfect, for Baseline Assessment G:

=IF('VISION AND MISSION'!G11=1,'VISION AND MISSION'!C11,IF('VISION AND MISSION'!G11=2,'VISION AND MISSION'!D11, IF('VISION AND MISSION'!G11=3,'VISION AND MISSION'!E11,"NA")))


I need exactly the same thing to happen for Next Assessment H, when both G and H have values, so that H overrides G

Am I making any sense? :D Must have more coffee!

Getting very complex!!!

It's easier to just bring the criteria in based on the 'SCORES AND ASSIGNED PRIORITIES' tab, instead of matching it to the other tabs.

I'm going to play with this now, and see if I can get it working for all possible conditions...easier I think.




=IF('SCORES AND ASSIGNED PRIORITIES '!G29="HIGH",'VISION AND MISSION'!C11)

Originally Posted by JohnTopley

Getting very complex!!!

Indeed....

which indicates there is a more simple way :D

Using the other criteria, I guess it is something along the lines of (untested!) ...



=IF('VISION AND MISSION'!H11="",IF('VISION AND MISSION'!G11=1,'VISION AND MISSION'!C11,IF('VISION AND MISSION'!G11=2,'VISION AND MISSION'!D11, IF('VISION AND MISSION'!G11=3,'VISION AND MISSION'!E11,"NA"))),IF('VISION AND MISSION'!H11=1,'VISION AND MISSION'!C11,IF('VISION AND MISSION'!H11=2,'VISION AND MISSION'!D11, IF('VISION AND MISSION'!H11=3,'VISION AND MISSION'!E11,"NA")))

This could be shortened by using the OFFSET function.

Well, this isn't very fun, LOL

Originally Posted by JohnTopley

Using the other criteria, I guess it is something along the lines of (untested!) ...



=IF('VISION AND MISSION'!H11="",IF('VISION AND MISSION'!G11=1,'VISION AND MISSION'!C11,IF('VISION AND MISSION'!G11=2,'VISION AND MISSION'!D11, IF('VISION AND MISSION'!G11=3,'VISION AND MISSION'!E11,"NA"))),IF('VISION AND MISSION'!H11=1,'VISION AND MISSION'!C11,IF('VISION AND MISSION'!H11=2,'VISION AND MISSION'!D11, IF('VISION AND MISSION'!H11=3,'VISION AND MISSION'!E11,"NA")))

This could be shortened by using the OFFSET function.

Tested it and I think this did it. Going to work the rest of the Action Plan tabs now, and see if it holds

=IF('VISION AND MISSION'!H11="",IF('VISION AND MISSION'!G11=1,'VISION AND MISSION'!C11,IF('VISION AND MISSION'!G11=2,'VISION AND MISSION'!D11,IF('VISION AND MISSION'!G11=3,'VISION AND MISSION'!E11,"NA"))),IF('VISION AND MISSION'!H11=1,'VISION AND MISSION'!C11,IF('VISION AND MISSION'!H11=2,'VISION AND MISSION'!D11,IF('VISION AND MISSION'!H11=3,'VISION AND MISSION'!E11,"NA"))))

I'm all ears on the OFFSET function....completely unfamiliar with it, but down for learning!

Completely untested (IFs might need adjusting to get "NA" correct):

Checks if G11 is number <=3, then use Offset from B11 so if G11=1, it will get C11: and same again for H11


=IF(AND(ISNUMBER('VISION AND MISSION'!G11),'VISION AND MISSION'!G11<=3),OFFSET('VISION AND MISSION'!$B11,,'VISION AND MISSION'!G11)),"NA"))),IF(AND(ISNUMBER('VISION AND MISSION'!H11),'VISION AND MISSION'!H11<=3),OFFSET('VISION AND MISSION'!$B11,,'VISION AND MISSION'!H11))
,"NA")))


Going out for a while!