Calculating using IF and AND to show numbers based on %

[bloemkool]

Hi,

I have an issues which should be solved in Excel, but I cannot seem to
find an answer, here's the problem.

In cel H2 is a percentage of target realisation.
In cel I2 is the formula which will give a numer of points based on
the percentage in H2.

E.G:
if H2 has the percentage 100% then cel I2 should show the number "30"
if H2 has the percentage 100,07% then cell I2 should show the number
"30"
if H2 has the percentage 102% then cell I2 should show the number "40"

This is the table on which the point are given:

0-100% = 0 (points)
100 till 101 = 30 (points)
101 till 105 = 40 (points)
105 + = 50 (points)

For some reason I cannot seem to get this to work...

Can anyone please help me out here as I am drastically stuck...

Thanks in advance

 

[Pete_UK]

Try this in I2:

=IF(H2>=1.05,50,IF(H2>=1.01,40,IF(H2>=1,30,0)))

Hope this helps.

Pete

 

[BloemkoOL]

Hi Pete,

For some reason I still get an error message....strange.
And of course the error messages in excel aren''t that helpfull.

In H2 is the following formula:

=(D2+E2+F2+G2)/C2

(This calculates the percentage of the 4 weeks in a month).

Could you pleaaaaaaase, check the formula if there is an error of some
kind, I tried looking at it, but my eyes started roling about.



Thanks in advance Pete!!!!

 

[BloemkoOL]

What I forgot to mention is the following VERY IMPORTANT part;

The values in between the 100 - 101 and the 101 - 105 also count.
So the values between 101-105 should result in 40 points...

 

[Pete_UK]

What error message do you get? If it is #DIV?0 then you need to check
that the value of C2 is not zero. If you get #VALUE then one of the
values in D2, E2, F2 or G2 could be text. There are no errors in the
formula I gave you, but if you copied it directly from the NG you
might have encountered a spurious line-wrap (this tends to give you
hyphens in the middle of the formula). Make sure you type in the full-
stops and commas as given in the formula - depending on where you are
based, you might have to use semi-colons instead of the commas.

Hope this helps.

Pete

 

[Pete_UK]

The first part of the formula checks if H2 is greater than or equal to
105% - if so, then it returns 50. The second part checks to see
whether it is larger or equal to 101% (and, because it will have got
past the first IF, it will be less than 105% - thus H2 can be any
number between 101% and 104.999999999 % inclusive to give 40 points.

Is this what you meant? It is unclear in your first example whether
101% belongs to the 30-point range or to the 40-point range, or
whether 105% should yield 40 points or 50 points. Please clarify

Pete

 

[BloemkoOL]

Do you have an email address?
I can send it to you....?

 

[Pete_UK]

OK, send it to:

pashurst <at> auditel.net

Make the obvious changes.

Pete