|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 29th, 2010, 03:14 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VALUE appearing (referencing blank cell)
Hi Guys, sorry for again posting on here but this reoprt will be the death of
me. I have 2 cells that have the following formlas IN C45 =IF(ISBLANK(AG149),"",AG149) IN D45 =IF(B45=1,'Base Data'!$H$52,IF(B45=2,'Base Data'!$I$52,IF(B45=3,'Base Data'!$J$52,IF(B45=4,'Base Data'!$K$52,IF(B45=5,'Base Data'!$L$52,IF(B45="pd",'Base Data'!$M$52,"")))))) Both C45 and D45 have no data in and are therefore blank - GREAT Then in cell AF45 I have the following formula which is linking to both C45 & D45 above (the AF cells referenced in the below formula both have values in FYI) =IF(ISBLANK($C45),"",IF((MIN((AF$4-$C45)+1,AF$3)/AF$3*$D45/12)=0,"",MIN((AF$4-$C45)+1,AF$3)/AF$3*$D45/12)) If there is data in C45 & D45 then the formula works fine. However when both C45 & D45 are blank I am getting ''VALUE'' returned. I have to have them blank to enable my sum to work. Thanks again for anyones help. I really appreciate it. 
|
|
#2
March 29th, 2010, 04:46 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VALUE appearing (referencing blank cell)
You're testing for C45 = blank but not D45.
So, does that mean if C45 is not blank but D45 is you still want to calculate the formula? Or, do you *not* want to do anything unless both C45 and D45 contain numbers? -- Biff Microsoft Excel MVP "Mark D" wrote in message ... Hi Guys, sorry for again posting on here but this reoprt will be the death of me. I have 2 cells that have the following formlas IN C45 =IF(ISBLANK(AG149),"",AG149) IN D45 =IF(B45=1,'Base Data'!$H$52,IF(B45=2,'Base Data'!$I$52,IF(B45=3,'Base Data'!$J$52,IF(B45=4,'Base Data'!$K$52,IF(B45=5,'Base Data'!$L$52,IF(B45="pd",'Base Data'!$M$52,"")))))) Both C45 and D45 have no data in and are therefore blank - GREAT Then in cell AF45 I have the following formula which is linking to both C45 & D45 above (the AF cells referenced in the below formula both have values in FYI) =IF(ISBLANK($C45),"",IF((MIN((AF$4-$C45)+1,AF$3)/AF$3*$D45/12)=0,"",MIN((AF$4-$C45)+1,AF$3)/AF$3*$D45/12)) If there is data in C45 & D45 then the formula works fine. However when both C45 & D45 are blank I am getting ''VALUE'' returned. I have to have them blank to enable my sum to work. Thanks again for anyones help. I really appreciate it.
|
|
#3
March 29th, 2010, 05:17 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VALUE appearing (referencing blank cell)
Ah yes sorry I should be testing both C45 & D45 to be blank.
Do you have any suggestions on how to add that into the forumla? Thanks for the reply "T. Valko" wrote: You're testing for C45 = blank but not D45. So, does that mean if C45 is not blank but D45 is you still want to calculate the formula? Or, do you *not* want to do anything unless both C45 and D45 contain numbers? -- Biff Microsoft Excel MVP "Mark D" wrote in message ... Hi Guys, sorry for again posting on here but this reoprt will be the death of me. I have 2 cells that have the following formlas IN C45 =IF(ISBLANK(AG149),"",AG149) IN D45 =IF(B45=1,'Base Data'!$H$52,IF(B45=2,'Base Data'!$I$52,IF(B45=3,'Base Data'!$J$52,IF(B45=4,'Base Data'!$K$52,IF(B45=5,'Base Data'!$L$52,IF(B45="pd",'Base Data'!$M$52,"")))))) Both C45 and D45 have no data in and are therefore blank - GREAT Then in cell AF45 I have the following formula which is linking to both C45 & D45 above (the AF cells referenced in the below formula both have values in FYI) =IF(ISBLANK($C45),"",IF((MIN((AF$4-$C45)+1,AF$3)/AF$3*$D45/12)=0,"",MIN((AF$4-$C45)+1,AF$3)/AF$3*$D45/12)) If there is data in C45 & D45 then the formula works fine. However when both C45 & D45 are blank I am getting ''VALUE'' returned. I have to have them blank to enable my sum to work. Thanks again for anyones help. I really appreciate it. .
|
|
#4
March 29th, 2010, 05:28 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VALUE appearing (referencing blank cell)
If you don't want to do anything unless both cells contain numbers start the
formula with: =IF(COUNT($C45,$D45)2,"",IF((MIN(............ -- Biff Microsoft Excel MVP "Mark D" wrote in message ... Ah yes sorry I should be testing both C45 & D45 to be blank. Do you have any suggestions on how to add that into the forumla? Thanks for the reply "T. Valko" wrote: You're testing for C45 = blank but not D45. So, does that mean if C45 is not blank but D45 is you still want to calculate the formula? Or, do you *not* want to do anything unless both C45 and D45 contain numbers? -- Biff Microsoft Excel MVP "Mark D" wrote in message ... Hi Guys, sorry for again posting on here but this reoprt will be the death of me. I have 2 cells that have the following formlas IN C45 =IF(ISBLANK(AG149),"",AG149) IN D45 =IF(B45=1,'Base Data'!$H$52,IF(B45=2,'Base Data'!$I$52,IF(B45=3,'Base Data'!$J$52,IF(B45=4,'Base Data'!$K$52,IF(B45=5,'Base Data'!$L$52,IF(B45="pd",'Base Data'!$M$52,"")))))) Both C45 and D45 have no data in and are therefore blank - GREAT Then in cell AF45 I have the following formula which is linking to both C45 & D45 above (the AF cells referenced in the below formula both have values in FYI) =IF(ISBLANK($C45),"",IF((MIN((AF$4-$C45)+1,AF$3)/AF$3*$D45/12)=0,"",MIN((AF$4-$C45)+1,AF$3)/AF$3*$D45/12)) If there is data in C45 & D45 then the formula works fine. However when both C45 & D45 are blank I am getting ''VALUE'' returned. I have to have them blank to enable my sum to work. Thanks again for anyones help. I really appreciate it. .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
