I need to create a two part formula

D

DMW

The formula below will return a GPA for our salespeople
=IF(H7>0.9,4,IF(H7>=0.8,3,IF(H7>=0.7,2,IF(H7>=0.6,1,0)))), but I need to add
one more criteria and that if another cell (C4) = 0 then the results of this
formula need to be 0 as well.

I hope this makes sense, can someone please help me.

Thanks!
 
F

Fred Smith

Something like:
=IF(C4=0,0,H7>0.9,4,IF(H7>=0.8,3,IF(H7>=0.7,2,IF(H7>=0.6,1,0)))))

Regards,
Fred
 
B

Bernard Liengme

Let's say goodbye to IF for this one
First try
=LOOKUP(H7*10,{0,6,7,8,9},{0,1,2,3,4})*(C4<>0)

But that throws up a 4 for H7 =0.9 and you have H7>0.9
How about
=LOOKUP(H7*10,{0,6,7,8,9.0000001},{0,1,2,3,4})*(C4<>0)
or
=(C4<>0)*((H7>=0.6)+(H7>=0.7)+(H7>=0.8)+(H7>0.9))
best wishes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top