Removing ''VALUES''

  • Thread starter Thread starter Mark D
  • Start date Start date
M

Mark D

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
 
Try

=IF(N(K76),(K76/K57),"")

Similarly if you want to check whether K57 holds anything check for that
within a OR()
 
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
 
Try this instead

=IF(AND(K98<>"",K98<70%,B117>=1,B117<=5),
K77*INDEX('Base Data'!I31:I35,B117),"")
 
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
Click to expand...

You could also replace the "" in your formula with a 0, i.e., a value.
Tou can't divide a text by a value hence the error.
 
Back
Top