| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal not working with #N/A in the cell
I have a hllookup formula that provides certain results as #N/A....I
understand why I get a #N/A, however, I need to subtotal the row. I tried to use the subtotal formula, but it does not recognize #N/A as a number, obviously cause it is not a number. I need to add these up for example: 10, 20, #N/A, #N/A, Subtotal: 30 How can I convert the #N/A so that they are "0" or blank so that my formula works? Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtotal not working with #N/A in the cell
=if(iserror(hlookup(...)),0,hlookup(...))
=if(iserror(hlookup(...)),"",hlookup(...)) to return 0 or to return an empty string (cell looks empty). Send Object Command - Two attachments wrote: I have a hllookup formula that provides certain results as #N/A....I understand why I get a #N/A, however, I need to subtotal the row. I tried to use the subtotal formula, but it does not recognize #N/A as a number, obviously cause it is not a number. I need to add these up for example: 10, 20, #N/A, #N/A, Subtotal: 30 How can I convert the #N/A so that they are "0" or blank so that my formula works? Thanks! -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtotal not working with #N/A in the cell
In the originating HLOOKUP use an IF(ISNA...combination
=IF(ISNA(YourHLOOKUP),0,YourHLOOKUP)) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Send Object Command - Two attachments" soft.com wrote in message ... I have a hllookup formula that provides certain results as #N/A....I understand why I get a #N/A, however, I need to subtotal the row. I tried to use the subtotal formula, but it does not recognize #N/A as a number, obviously cause it is not a number. I need to add these up for example: 10, 20, #N/A, #N/A, Subtotal: 30 How can I convert the #N/A so that they are "0" or blank so that my formula works? Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtotal not working with #N/A in the cell
Correction
IF(ISNA(YourHLOOKUP),0,YourHLOOKUP) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... In the originating HLOOKUP use an IF(ISNA...combination =IF(ISNA(YourHLOOKUP),0,YourHLOOKUP)) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Send Object Command - Two attachments" soft.com wrote in message ... I have a hllookup formula that provides certain results as #N/A....I understand why I get a #N/A, however, I need to subtotal the row. I tried to use the subtotal formula, but it does not recognize #N/A as a number, obviously cause it is not a number. I need to add these up for example: 10, 20, #N/A, #N/A, Subtotal: 30 How can I convert the #N/A so that they are "0" or blank so that my formula works? Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtotal not working with #N/A in the cell
Works great! Thank You.
"Nick Hodge" wrote: Correction IF(ISNA(YourHLOOKUP),0,YourHLOOKUP) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... In the originating HLOOKUP use an IF(ISNA...combination =IF(ISNA(YourHLOOKUP),0,YourHLOOKUP)) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Send Object Command - Two attachments" soft.com wrote in message ... I have a hllookup formula that provides certain results as #N/A....I understand why I get a #N/A, however, I need to subtotal the row. I tried to use the subtotal formula, but it does not recognize #N/A as a number, obviously cause it is not a number. I need to add these up for example: 10, 20, #N/A, #N/A, Subtotal: 30 How can I convert the #N/A so that they are "0" or blank so that my formula works? Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtotal not working with #N/A in the cell
Great
-- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Send Object Command - Two attachments" soft.com wrote in message ... Works great! Thank You. "Nick Hodge" wrote: Correction IF(ISNA(YourHLOOKUP),0,YourHLOOKUP) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... In the originating HLOOKUP use an IF(ISNA...combination =IF(ISNA(YourHLOOKUP),0,YourHLOOKUP)) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Send Object Command - Two attachments" soft.com wrote in message ... I have a hllookup formula that provides certain results as #N/A....I understand why I get a #N/A, however, I need to subtotal the row. I tried to use the subtotal formula, but it does not recognize #N/A as a number, obviously cause it is not a number. I need to add these up for example: 10, 20, #N/A, #N/A, Subtotal: 30 How can I convert the #N/A so that they are "0" or blank so that my formula works? Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell References | Excel Discussion (Misc queries) | |||
| Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
| insert date | Excel Worksheet Functions | |||
| Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
| Can I concatenate text in cells to make a working formula? | Excel Discussion (Misc queries) | |||
Linear Mode
