|
| 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 25th, 2010, 01:28 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 
|
|
#2
March 25th, 2010, 01:39 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#3
March 25th, 2010, 01:43 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#4
March 25th, 2010, 01:47 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing ''VALUES''
Thank you Jacob
Lastly I know have the following formula that links to the one that you helped me make blank =(SUMPRODUCT((K9870%)*($B117=1),K77*'Base Data'!$I$31))+(SUMPRODUCT((K9870%)*($B117=2),K77* 'Base Data'!$I$32))+(SUMPRODUCT((K9870%)*($B117=3),K77* 'Base Data'!$I$33))+(SUMPRODUCT((K9870%)*($B117=4),K77* 'Base Data'!$I$34))+(SUMPRODUCT((K9870%)*($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
|
|
#5
March 25th, 2010, 02:12 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing ''VALUES''
Try this instead
=IF(AND(K98"",K9870%,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((K9870%)*($B117=1),K77*'Base Data'!$I$31))+(SUMPRODUCT((K9870%)*($B117=2),K77* 'Base Data'!$I$32))+(SUMPRODUCT((K9870%)*($B117=3),K77* 'Base Data'!$I$33))+(SUMPRODUCT((K9870%)*($B117=4),K77* 'Base Data'!$I$34))+(SUMPRODUCT((K9870%)*($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
|
|
#6
March 25th, 2010, 08:13 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
