Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to ignore #NA in subtotals?
I have several data tables of identical layout that are updated daily, the
first two manually and the others by preset formula that calculates from the first two. If a zero or no data are input into cells in the first two then the formula of the others will return #NA, this is intentional because charts are plotted from the latter tables and #NA prevents zero's being plotted all over it for data that has not been entered yet (zero is a valid in negative and positive data entry only when entered). Each table has a SUM total, but, if #NA exits in any one of the cells totaled the SUM is returned #NA. How can I overcome this? |
#2
|
|||
|
|||
One way is to use
=if(isna(your_function),"N/A",your_function) intead of allowing the #N/A On Wed, 2 Mar 2005 09:53:02 -0800, Lee IT wrote: I have several data tables of identical layout that are updated daily, the first two manually and the others by preset formula that calculates from the first two. If a zero or no data are input into cells in the first two then the formula of the others will return #NA, this is intentional because charts are plotted from the latter tables and #NA prevents zero's being plotted all over it for data that has not been entered yet (zero is a valid in negative and positive data entry only when entered). Each table has a SUM total, but, if #NA exits in any one of the cells totaled the SUM is returned #NA. How can I overcome this? |
#3
|
|||
|
|||
I usually use two columns when I do this--one for the real values and one to be
used for charting (with the #n/a's). But you could do something like: =sum(if(isnumber(a1:a30),a1:a30)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Lee IT wrote: I have several data tables of identical layout that are updated daily, the first two manually and the others by preset formula that calculates from the first two. If a zero or no data are input into cells in the first two then the formula of the others will return #NA, this is intentional because charts are plotted from the latter tables and #NA prevents zero's being plotted all over it for data that has not been entered yet (zero is a valid in negative and positive data entry only when entered). Each table has a SUM total, but, if #NA exits in any one of the cells totaled the SUM is returned #NA. How can I overcome this? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotals Problem when Filtering | Excel Discussion (Misc queries) | |||
In Excel, how do you copy and paste just the subtotals into anoth. | Excel Discussion (Misc queries) | |||
Pivot Table subtotals | Excel Worksheet Functions | |||
PASTE SUBTOTALS IN EXCEL TO ANOTHER SHEET TO DO ANOTHER SORT | Excel Discussion (Misc queries) | |||
why are nested subtotals coming out below outer subtotals? | Excel Worksheet Functions |