Removing ''VALUES''
 

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,'Person al 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

Removing ''VALUES''
 

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,'Person al 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

Removing ''VALUES''
 

=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,'Person al 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

Removing ''VALUES''
 

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,'Person al 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

Removing ''VALUES''
 

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,'Person al 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

Removing ''VALUES''
 

On Mar 25, 7:28*am, 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,'Person al 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

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.