Formula problem

[Guest]

Hi there, I'm hoping that this is the correct Forum for this question!!

I've got a problem with a complex formula and was hoping that some hepful
soul could help me as I'm totally stuck!

Please see below for details:

I need the cell to read from the value in an adjacent cell according to the
following rules:


% logged in
Score

103.9%
5


If C7 is equal to, or less than, 95% then the score will be 0

If C7 is equal to, or greater than, 95.1, but equal to or lower than 98.0,
then the score will be 1

If C7 is equal to, or greater than, 98.1, but equal to or lower than 100.0,
then the score will be 2

If C7 is equal to, or greater than, 100.1, but equal to or lower than 102.0,
then the score will be 3

If C7 is equal to, or greater than, 102.1, but equal to or lower than 103.0,
then the score will be 4

If C7 is equal to, or greater than 103.1, then the score will be 5

The formula I have at the moment (and this is after weeks of trying alone)
is thus….



=IF(C7<95,0)*(IF((OR(C7>95,C7<98)),1))*(IF((OR(C7>98,C7<100)),2))*(IF((OR(C7>100,C7<102)),3))*(IF((OR(C7>102,C7<103)),4))*(IF(C7>103,5))



….and it keeps giving me the value 0



What do you think??

 

[Dave O]

You're pretty close. Instead of writing separate IF statements you
need to nest them so the "false" argument is the beginning of the next
IF. Additionally, you have OR statements when you need AND statements,
and the "greater than" and "less than" should in many cases be "greater
than or equal to" and "less than or equal to".

The reason your original formula generates a 0 is because any single IF
that returns a zero multiplies every other zero or non-zero response by
zero, resulting in zero.

 

[CLR]

=IF(C7>=103.1,5,IF(C7>=102.1,4,IF(C7>=100.1,3,IF(C7>=98.1,2,IF(C7>=95.1,1,IF
(C7>=95,0,""))))))

All on one line, watch out for email word-wrap

Vaya con Dios,
Chuck, CABGx3

 

[Niek Otten]

Create a small table, in this example in A1:B7

0.00% 0
95.10% 1
98.10% 2
100.10% 3
102.10% 4
103.10% 5
9999.00% #N/A


Now your formula is:

=VLOOKUP(C7,A1:B6,2)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Sandy Mann]

Giff,

Try:


=(C7>95)+(C7>98)+(C7>100)+(C7>102)+(C7>103)

or

=SUM(--(C7>{95,98,100,102,103}))

HTH

Sandy