Help with IF function

[kuhfku]

Hi. I'm using Excel 2010 in Windows 7.

I work in a College and I am working on this mark sheet: http://i.imgur.com/Ox6PPx5.png (I tried using the insert image button but nothing happened)

In Cell H26 I have the following formula:

=IF(H25>=1590,"D*D*D*",IF(H25>=1560,"DD*D*",IF(H25>=1530,"DDD*",IF(H25>=1500,"DDD",IF(H25>=1460,"DDM",IF(H25>=1420,"DMM",IF(H25>=1380,"MMM",IF(H25>=1340,"MMP",IF(H25>=1300,"MPP",IF(H25>=1260,"PPP",IF(H25<=1259,"Incomplete")))))))))))


I occurred to me after writing the above formula that I actually need a formula that shows "Incomplete" in H26 if any of the points cells show "0" as all units need to be completed to achieve the qualification. I attempted to use an OR function like so:

=IF(H25>=1590,"D*D*D*",IF(H25>=1560,"DD*D*",IF(H25>=1530,"DDD*",IF(H25>=1500,"DDD",IF(H25>=1460,"DDM",IF(H25>=1420,"DMM",IF(H25>=1380,"MMM",IF(H25>=1340,"MMP",IF(H25>=1300,"MPP",IF(H25>=1260,"PPP",IF(OR(G2<1,G3<1,G4<1,G5<1,G6<1,G7<1,G8<1,G9<1,G10<1,G11<1,G12<1,G13<1,G14<1,G15<1,G16<1,G17<1,G18<1,G19<1,
G20<1,G21<1,G22<1,G23<1,G24<1),H25<=1259,"Incomplete")))))))))))

but that doesn't work. I just get the same result as with the original formula.

Having the OR section at the beginning of the function results in a "too many arguments for this function" error.

I'm not sure what kind of function I really need here.

To clarify, if any of the cells G2 - G24 have a 0 in them I want cell H26 to say "Incomplete", if all the cells are over 0 then the overall grade should be shown (as worked out in the first formula above).

Thanks

 

[Special-K99]

1) Regarding your original formula...

Change your original formula.
Create a table with "H25 values" (1590,1560,1530 etc) in the first column
In the second column place your results, the D M and P values

Then simply use a use a VLOOKUP in column A to retrieve the correct value from column B

2) Then insert the VLOOKUP formula as below

IF(COUNTIF(G2:G24,0)>0,"Incomplete",VLOOKUP etc...)

 

[kuhfku]

Thanks. I've not used VLOOKUP before but following some basics I found online I came up with this formula:

=IF(COUNTIF(G2:G24,0)>0,"Incomplete",VLOOKUP(H25,K1:L10,2,TRUE)) [where K1:L10 is the table of values]

I do now get "Incomplete" in H26 when there are 0's in column G but I don't get the correct result showing when all the results are entered. How can I use VLOOKUP where the figure in H25 will be greater than or equal to a figure in my table of values? A quick google suggests not. So do I need my table of values to show all integers between 1260 to 1620 (the maximum)?

 

[Jonmo1]

Make sure the table (K1:L10) is sorted in AScending order by column K.

 

[pjmorris]

Try this

=IF(COUNTIF(G2:G24,0)>0, "Incomplete", IF(H25>=1590,"D*D*D*",IF(H25>=1560,"DD*D*",IF(H25>=1530,"DDD*",IF(H25>=1500,"DDD",IF(H25>=1460,"DDM",IF(H25>=1420,"DMM",IF(H25>=1380,"MMM",IF(H25>=1340,"MMP",IF(H25>=1300,"MPP",IF(H25>=1260,"PPP",IF(H25<=1259,"Incomplete"))))))))))))

 

[kuhfku]

Jonmo, that didn't have any affect.

pj, I did that when I couldn't get the VLOOKUP to work. I think I'm gonna stick with that.

Thanks for your help everyone!

 

[pjmorris]

delighted to have helped, many thanks for the feedback..

Regards