|
ISNUMBER and IF Functions
Ok, lets see if i can explain this one well enough...
I have 3 columns, A,B and C. In these columns are numbers. In Column D I want to apply the following formula: =A1+MAX(B1/365,C1/6) This works however the problems arrise as there are some cells in columns A:C which are blank or have text not numbers. I tried to fix this with this formula: =ISNUMBER(A1:C1),A1+MAX(B1/365,C1/6) This works however if there is no number in any one of the three cells it returns a "FALSE" error. I would prefere it to display nothing, ie "". But i can't seem to do this. Other more complex problems: if there is one number in either column B or C i would like it to complete the formula using this number. There must always be a number in column A. If there is no number in A then the result should be blank. example: A B C D 4 6 7 calculate 6 7 Nothing text 6 7 Nothing 4 text 7 calculate 4 Nothing 4 text text Nothing 4 text Nothing Hopefully someone can help me. It is driving me crazy! Jon Scott |
ISNUMBER and IF Functions
"spunkyjon" skrev i en meddelelse
ps.com... Ok, lets see if i can explain this one well enough... I have 3 columns, A,B and C. In these columns are numbers. In Column D I want to apply the following formula: =A1+MAX(B1/365,C1/6) This works however the problems arrise as there are some cells in columns A:C which are blank or have text not numbers. I tried to fix this with this formula: =ISNUMBER(A1:C1),A1+MAX(B1/365,C1/6) This works however if there is no number in any one of the three cells it returns a "FALSE" error. I would prefere it to display nothing, ie "". But i can't seem to do this. Other more complex problems: if there is one number in either column B or C i would like it to complete the formula using this number. There must always be a number in column A. If there is no number in A then the result should be blank. example: A B C D 4 6 7 calculate 6 7 Nothing text 6 7 Nothing 4 text 7 calculate 4 Nothing 4 text text Nothing 4 text Nothing Hopefully someone can help me. It is driving me crazy! Jon Scott Hi Jon Try this one: =IF(NOT(ISNUMBER(A1)),"",IF(AND(ISNUMBER(B1),NOT(I SNUMBER(C1))), A1+B1/365,IF(AND(NOT(ISNUMBER(B1)),ISNUMBER(C1)),A1+C1/6, IF(AND(ISNUMBER(B1),ISNUMBER(C1)),A1+MAX(B1/365,C1/6),"")))) assuming 6 7 Nothing should have been 6 7 Calculate (number in B) -- Best regards Leo Heuser Followup to newsgroup only please. |
ISNUMBER and IF Functions
Thanks guys that worked like a charm!!!
:) paul wrote: =IF(A1="","",IF(AND(NOT(ISNUMBER(B1)),ISNUMBER(C1) ),A1+C1/6,IF(AND(NOT(ISNUMBER(C1)),ISNUMBER(B1)),A1+B1/365,A1+MAX(B1/365,C1/6)))) i think this covers what you have posted.There may be another couple of conditions tho.Could there be non numbers in B and C?.Will b1/365 and c1/6 ever be equal? -- paul remove nospam for email addy! "spunkyjon" wrote: Ok, lets see if i can explain this one well enough... I have 3 columns, A,B and C. In these columns are numbers. In Column D I want to apply the following formula: =A1+MAX(B1/365,C1/6) This works however the problems arrise as there are some cells in columns A:C which are blank or have text not numbers. I tried to fix this with this formula: =ISNUMBER(A1:C1),A1+MAX(B1/365,C1/6) This works however if there is no number in any one of the three cells it returns a "FALSE" error. I would prefere it to display nothing, ie "". But i can't seem to do this. Other more complex problems: if there is one number in either column B or C i would like it to complete the formula using this number. There must always be a number in column A. If there is no number in A then the result should be blank. example: A B C D 4 6 7 calculate 6 7 Nothing text 6 7 Nothing 4 text 7 calculate 4 Nothing 4 text text Nothing 4 text Nothing Hopefully someone can help me. It is driving me crazy! Jon Scott |
ISNUMBER and IF Functions
"spunkyjon" skrev i en meddelelse
oups.com... Thanks guys that worked like a charm!!! :) You're welcome |
| All times are GMT +1. The time now is 06:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com