Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trouble with null values
Hi all,
Can someone please help me with a problem I'm having using an IF statement that refers to cells that may have a null value. I've created a sheet for recording student exam results. Column D shows how many major errors have been made, Column E shows how many minor errors have been made, Column F gives a P or F depending on the amount of errors made; 0 major and 4 or less minor are a pass, 1 major or 5 or more minor are a fail. The formula looks like this; =IF(D3=1,"F",IF(E34,"F","P")) and works fine. However if columns D and E are empty, then I don't want anything displayed in column F, at the moment all cells in column F are displaying a P. I think that it may involve more nested IFs for the formula but I seem to be going around in circles. Hope someone can help sort this for me, Thanks, Tazzy -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200712/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trouble with null values
=IF(OR(ISBLANK(D3),ISBLANK(E3)),"",IF(D3=1,"F",IF (E34,"F","P")))
Regards, Stefi Tazzy via OfficeKB.com ezt *rta: Hi all, Can someone please help me with a problem I'm having using an IF statement that refers to cells that may have a null value. I've created a sheet for recording student exam results. Column D shows how many major errors have been made, Column E shows how many minor errors have been made, Column F gives a P or F depending on the amount of errors made; 0 major and 4 or less minor are a pass, 1 major or 5 or more minor are a fail. The formula looks like this; =IF(D3=1,"F",IF(E34,"F","P")) and works fine. However if columns D and E are empty, then I don't want anything displayed in column F, at the moment all cells in column F are displaying a P. I think that it may involve more nested IFs for the formula but I seem to be going around in circles. Hope someone can help sort this for me, Thanks, Tazzy -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200712/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trouble with null values
=IF(AND(D3="",E3=""),"",IF(D3=1,"F",IF(E34,"F"," P")))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tazzy via OfficeKB.com" <u26845@uwe wrote in message news:7c462a672113c@uwe... Hi all, Can someone please help me with a problem I'm having using an IF statement that refers to cells that may have a null value. I've created a sheet for recording student exam results. Column D shows how many major errors have been made, Column E shows how many minor errors have been made, Column F gives a P or F depending on the amount of errors made; 0 major and 4 or less minor are a pass, 1 major or 5 or more minor are a fail. The formula looks like this; =IF(D3=1,"F",IF(E34,"F","P")) and works fine. However if columns D and E are empty, then I don't want anything displayed in column F, at the moment all cells in column F are displaying a P. I think that it may involve more nested IFs for the formula but I seem to be going around in circles. Hope someone can help sort this for me, Thanks, Tazzy -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200712/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trouble with null values
Or another way...
=IF(D3="","",IF(E3="","",IF(D3=1,"F",IF(E34,"F", "P")))) Hope this helps, Gav. "Tazzy via OfficeKB.com" wrote: Hi all, Can someone please help me with a problem I'm having using an IF statement that refers to cells that may have a null value. I've created a sheet for recording student exam results. Column D shows how many major errors have been made, Column E shows how many minor errors have been made, Column F gives a P or F depending on the amount of errors made; 0 major and 4 or less minor are a pass, 1 major or 5 or more minor are a fail. The formula looks like this; =IF(D3=1,"F",IF(E34,"F","P")) and works fine. However if columns D and E are empty, then I don't want anything displayed in column F, at the moment all cells in column F are displaying a P. I think that it may involve more nested IFs for the formula but I seem to be going around in circles. Hope someone can help sort this for me, Thanks, Tazzy -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200712/1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trouble with null values
Bob/Stefi,
Thank you both for such a quick answer, have saved both of your formulae in my ever growing list. Spreadsheet now looks the way I want it to. Kind Reagrds, Tazzy -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200712/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trouble with null values
Thanks to you as well Gav,
Tazzy -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200712/1 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trouble with null values
You are welcome! Thanks for the feedback!
Stefi Tazzy via OfficeKB.com ezt *rta: Bob/Stefi, Thank you both for such a quick answer, have saved both of your formulae in my ever growing list. Spreadsheet now looks the way I want it to. Kind Reagrds, Tazzy -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200712/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to replace null values with zero? | Setting up and Configuration of Excel | |||
Null values need to be replaced | Excel Discussion (Misc queries) | |||
sum if - null values | Excel Discussion (Misc queries) | |||
VLookup Null values | Excel Worksheet Functions | |||
return a " " for null values | Excel Worksheet Functions |