Help with an "If Statement"

[Excellwannabee]

Background:
Our projects are divided into Categories on a spreadsheet and we have a
criteria for the overall Red, Amber, Green Status that we give for each
category.
I have managed to use the Countif formula to count the Red, Amber and Green
status for each catergory. I used a nested If statment but it keep returning
a #Value!

The criteria is % of Amber and/or Red is more than 30% of overall for that
category then it is Red.
If % of Amber and/or Red is more than 20% but less than 29% then overall
status for the category should be Amber.
If % of Amber and/or Red is less than 19% then overall status for the
category should be Green.
Please can someone tell me how I can use the above criteria on a range of
cells with percentages to be able to give me the Red, Amber or Green Status
for a category?

 

[Max]

For better answers, post your formula(s) ...
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

 

[Excellwannabee]

Apologies:
=IF(IS27:IS31>30%,"R",IF(IS27:IS31>20%<29%,"A", IF(IS27:IS31>19%,"G","G")))

IS27:IS31 is the range where the data is in.

 

[Excellwannabee]

I am using xl 2003
this is the formula I used: =IF(IS27:IS31>30%,"R",IF(IS27:IS31>20%<29%,"A",
IF(IS27:IS31>19%,"G","G"))), where IS27:IS31 is the data range.

 

[David Biddulph]

Firstly you are looking at a range of values, so you need to enter the
formula as an array formula (Control Shift Enter)
Secondly your second IF statement has illegal syntax. Look up the AND
function in Excel help.
Thirdly your third IF does nothing useful, as it returns the same result for
either outcome of the test.
Fourthly you haven't defined specifically what outcome you want for values
between 29% and 30% (inclusive), and it's not obvious what you were trying
to get for values between 19 and 20% (inclusive) [but see the 3rd point].
Fifthly you probably don't need the AND for the second test if you define
the other tests appropriately. Remember that you don't get to the second
test if you satisfy the first condition, so in this sort of case you usually
only need to define one limit at a time.
Sixthly you've snipped your original question, so nobody can see what you
were trying to ask in the first place. You need to quote enough of the
earlier messages to put your reply into context.
Sevently .. oh, that'll do for now.

 

[Max]

Perhaps you meant to do something like this
in say, IT27, copied down:
=IF(IS27>=30%,"R",IF(IS27>=20%,"A",IF(IS27>=19%,"G","")))
Test and modify to suit

I'm not sure that your expression is valid/works even with array-entering
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[David Biddulph]

No, I think you're right. It will only look at the first cell in the range.
Array entering gets rid of the #VALUE! error, but doesn't actually get it to
use the full array. It isn't clear, of course whether the OP wanted OR or
AND to apply to his range and his conditions.

 

[Shane Devenshire]

Hi,

There may be a problem with the problem as stated - 30% and 20% don't fit
into any category? On the other hand lets suppose your want somethning like
this: (array entered)

=IF(Range>30%,"R",IF(Range>20%,"A","G"))

However, it might be helpful to see a sample of the data because it is not
really clear what you are doing. Include the COUNTIF function you are
using.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire