Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with an "If Statement"
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with an "If Statement"
For better answers, post your formula(s) ...
-- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with an "If Statement"
Which version of XL are you using? -- Pecoflyer Cheers - *_Membership_is_free_* & allows file upload -faster and better answers ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46914 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with an "If Statement"
Apologies:
=IF(IS27:IS3130%,"R",IF(IS27:IS3120%<29%,"A", IF(IS27:IS3119%,"G","G"))) IS27:IS31 is the range where the data is in. "Max" wrote: For better answers, post your formula(s) ... -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with an "If Statement"
I am using xl 2003
this is the formula I used: =IF(IS27:IS3130%,"R",IF(IS27:IS3120%<29%,"A", IF(IS27:IS3119%,"G","G"))), where IS27:IS31 is the data range. "Pecoflyer" wrote: Which version of XL are you using? -- Pecoflyer Cheers - *_Membership_is_free_* & allows file upload -faster and better answers ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46914 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with an "If Statement"
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. -- David Biddulph "Excellwannabee" wrote in message ... Apologies: =IF(IS27:IS3130%,"R",IF(IS27:IS3120%<29%,"A", IF(IS27:IS3119%,"G","G"))) IS27:IS31 is the range where the data is in. "Max" wrote: For better answers, post your formula(s) ... -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with an "If Statement"
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 --- "Excellwannabee" wrote: Apologies: =IF(IS27:IS3130%,"R",IF(IS27:IS3120%<29%,"A", IF(IS27:IS3119%,"G","G"))) IS27:IS31 is the range where the data is in. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with an "If Statement"
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. -- David Biddulph "Max" wrote in message ... 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 --- "Excellwannabee" wrote: Apologies: =IF(IS27:IS3130%,"R",IF(IS27:IS3120%<29%,"A", IF(IS27:IS3119%,"G","G"))) IS27:IS31 is the range where the data is in. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with an "If Statement"
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(Range30%,"R",IF(Range20%,"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 "Excellwannabee" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining "IF"statement with "Vlookup" | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
embedding "ISERROR" function into an "IF" statement | Excel Worksheet Functions | |||
"IF" statement & "strikethrough" formatting | Excel Worksheet Functions |