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(C795,C7<98)),1))*(IF((OR(C7 98,C7<100)),2))*(IF((OR(C7100,C7<102)),3))*(IF((O R(C7102,C7<103)),4))*(IF(C7103,5))



€¦.and it keeps giving me the value 0



What do you think??

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.

=IF(C7=103.1,5,IF(C7=102.1,4,IF(C7=100.1,3,IF(C 7=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


"Giff" wrote in message
...
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(C795,C7<98)),1))*(IF((OR(C7 98,C7<100)),2))*(IF((OR(C7
100,C7<102)),3))*(IF((OR(C7102,C7<103)),4))*(IF( C7103,5))



..and it keeps giving me the value 0



What do you think??

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

"Giff" wrote in message
...
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(C795,C7<98)),1))*(IF((OR(C7 98,C7<100)),2))*(IF((OR(C7100,C7<102)),3))*(IF((O R(C7102,C7<103)),4))*(IF(C7103,5))



€¦.and it keeps giving me the value 0



What do you think??

Giff,

Try:


=(C795)+(C798)+(C7100)+(C7102)+(C7103)

or

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

HTH

Sandy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Giff" wrote in message
...
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(C795,C7<98)),1))*(IF((OR(C7 98,C7<100)),2))*(IF((OR(C7
100,C7<102)),3))*(IF((OR(C7102,C7<103)),4))*(IF( C7103,5))



..and it keeps giving me the value 0



What do you think??

On Thu, 31 Mar 2005 05:43:02 -0800, "Giff"
wrote:

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



=VLOOKUP(C7,{0,0;0.951,1;0.981,2;1.001,3;1.021,4;1 .031,5},2)

Note that you do not define what the score should be if the percent is between
95% and 95.1%, etc.

I assumed that anything less than n.1% would be treated the same as n.0%. If
that is not the case, you may need to make some changes in the array constant.

The array can also be set up in a range of cells and referred to by the range.
It is much more legible and easier to maintain that way. See VLOOKUP worksheet
function in HELP for further details.


--ron