Removing ''VALUES''
in [Functions]
|
From: Mark D on 25 Mar 2010 09:28 Hi Again (where would I be without the help from this board) I have a forumula in a cell as follows =IF(ISNA(VLOOKUP($A77,'Personal NFI 2010'!$1:$65536,14,FALSE)),"",VLOOKUP($A77,'Personal NFI 2010'!$1:$65536,14,FALSE)) working absolutely perfectly and if there is no number to find the cell stays blank. I then have the following formula that links to the one above =IF(ISBLANK(K76),"",(K76/K57)) Unfortunately if the cell is blank it's returning #VALUE! I need to get this removed as I can't sum the total of the rows if there are VALUES in there Again thanks for any help Mark
From: Jacob Skaria on 25 Mar 2010 09:39 Try =IF(N(K76),(K76/K57),"") Similarly if you want to check whether K57 holds anything check for that within a OR() -- Jacob "Mark D" wrote: > Hi Again (where would I be without the help from this board) > > I have a forumula in a cell as follows > > > =IF(ISNA(VLOOKUP($A77,'Personal NFI > 2010'!$1:$65536,14,FALSE)),"",VLOOKUP($A77,'Personal NFI > 2010'!$1:$65536,14,FALSE)) > > working absolutely perfectly and if there is no number to find the cell > stays blank. > > I then have the following formula that links to the one above > > =IF(ISBLANK(K76),"",(K76/K57)) > > Unfortunately if the cell is blank it's returning #VALUE! > > I need to get this removed as I can't sum the total of the rows if there are > VALUES in there > > Again thanks for any help > > Mark
From: RonaldoOneNil on 25 Mar 2010 09:43 =IF(K76="","",K76/K57) "Mark D" wrote: > Hi Again (where would I be without the help from this board) > > I have a forumula in a cell as follows > > > =IF(ISNA(VLOOKUP($A77,'Personal NFI > 2010'!$1:$65536,14,FALSE)),"",VLOOKUP($A77,'Personal NFI > 2010'!$1:$65536,14,FALSE)) > > working absolutely perfectly and if there is no number to find the cell > stays blank. > > I then have the following formula that links to the one above > > =IF(ISBLANK(K76),"",(K76/K57)) > > Unfortunately if the cell is blank it's returning #VALUE! > > I need to get this removed as I can't sum the total of the rows if there are > VALUES in there > > Again thanks for any help > > Mark
From: Mark D on 25 Mar 2010 09:47 Thank you Jacob Lastly I know have the following formula that links to the one that you helped me make blank =(SUMPRODUCT((K98<70%)*($B117=1),K77*'Base Data'!$I$31))+(SUMPRODUCT((K98<70%)*($B117=2),K77*'Base Data'!$I$32))+(SUMPRODUCT((K98<70%)*($B117=3),K77*'Base Data'!$I$33))+(SUMPRODUCT((K98<70%)*($B117=4),K77*'Base Data'!$I$34))+(SUMPRODUCT((K98<70%)*($B117=5),K77*'Base Data'!$I$35)) Can I add the same suggestion you gave me just now to make the cell blank. Again I am getting VALUE where there is no data (In this case K98 is blank). I don't know where I would necessarily add it Thanks for your help "Jacob Skaria" wrote: > Try > > =IF(N(K76),(K76/K57),"") > > Similarly if you want to check whether K57 holds anything check for that > within a OR() > > -- > Jacob > > > "Mark D" wrote: > > > Hi Again (where would I be without the help from this board) > > > > I have a forumula in a cell as follows > > > > > > =IF(ISNA(VLOOKUP($A77,'Personal NFI > > 2010'!$1:$65536,14,FALSE)),"",VLOOKUP($A77,'Personal NFI > > 2010'!$1:$65536,14,FALSE)) > > > > working absolutely perfectly and if there is no number to find the cell > > stays blank. > > > > I then have the following formula that links to the one above > > > > =IF(ISBLANK(K76),"",(K76/K57)) > > > > Unfortunately if the cell is blank it's returning #VALUE! > > > > I need to get this removed as I can't sum the total of the rows if there are > > VALUES in there > > > > Again thanks for any help > > > > Mark
From: Jacob Skaria on 25 Mar 2010 10:12
Try this instead =IF(AND(K98<>"",K98<70%,B117>=1,B117<=5), K77*INDEX('Base Data'!I31:I35,B117),"") -- Jacob "Mark D" wrote: > Thank you Jacob > > Lastly I know have the following formula that links to the one that you > helped me make blank > > =(SUMPRODUCT((K98<70%)*($B117=1),K77*'Base > Data'!$I$31))+(SUMPRODUCT((K98<70%)*($B117=2),K77*'Base > Data'!$I$32))+(SUMPRODUCT((K98<70%)*($B117=3),K77*'Base > Data'!$I$33))+(SUMPRODUCT((K98<70%)*($B117=4),K77*'Base > Data'!$I$34))+(SUMPRODUCT((K98<70%)*($B117=5),K77*'Base Data'!$I$35)) > > Can I add the same suggestion you gave me just now to make the cell blank. > Again I am getting VALUE where there is no data (In this case K98 is blank). > I don't know where I would necessarily add it > > Thanks for your help > > "Jacob Skaria" wrote: > > > Try > > > > =IF(N(K76),(K76/K57),"") > > > > Similarly if you want to check whether K57 holds anything check for that > > within a OR() > > > > -- > > Jacob > > > > > > "Mark D" wrote: > > > > > Hi Again (where would I be without the help from this board) > > > > > > I have a forumula in a cell as follows > > > > > > > > > =IF(ISNA(VLOOKUP($A77,'Personal NFI > > > 2010'!$1:$65536,14,FALSE)),"",VLOOKUP($A77,'Personal NFI > > > 2010'!$1:$65536,14,FALSE)) > > > > > > working absolutely perfectly and if there is no number to find the cell > > > stays blank. > > > > > > I then have the following formula that links to the one above > > > > > > =IF(ISBLANK(K76),"",(K76/K57)) > > > > > > Unfortunately if the cell is blank it's returning #VALUE! > > > > > > I need to get this removed as I can't sum the total of the rows if there are > > > VALUES in there > > > > > > Again thanks for any help > > > > > > Mark |