Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal function 1 that excludes "#VALUE!"
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal function 1 that excludes "#VALUE!"
Please place the question in the body of the email, all I can read with my
browser is the following when I hit reply: "An error occurred while sending your post -------------------------------------------------------------------------------- We're sorry, but there was a problem with the system and your post was not received. The error has been reported to Operations and will be investigated as soon as possible. Please try again later." -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JAbels001" wrote in message ... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal function 1 that excludes "#VALUE!"
This happens when there is no text in the body (as in this post).
We have been requesting all users to describe the problem in the text of the post but not everyone does that... :-( "Shane Devenshire" wrote: Please place the question in the body of the email, all I can read with my browser is the following when I hit reply: "An error occurred while sending your post -------------------------------------------------------------------------------- We're sorry, but there was a problem with the system and your post was not received. The error has been reported to Operations and will be investigated as soon as possible. Please try again later." -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JAbels001" wrote in message ... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal function 1 that excludes "#VALUE!"
Sorry, I was trying to reply and describe the problem but it wouldnt let me
until someone responded. I'm trying to calculate a subtotal formula b/c the data I'm using can be filtered and I want it to update whenever it is changed. The problem is that some of the values are "#VALUE!" which results in the subtotal formula coming back as #VALUE!. Please help... "Sheeloo" wrote: This happens when there is no text in the body (as in this post). We have been requesting all users to describe the problem in the text of the post but not everyone does that... :-( "Shane Devenshire" wrote: Please place the question in the body of the email, all I can read with my browser is the following when I hit reply: "An error occurred while sending your post -------------------------------------------------------------------------------- We're sorry, but there was a problem with the system and your post was not received. The error has been reported to Operations and will be investigated as soon as possible. Please try again later." -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JAbels001" wrote in message ... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal function 1 that excludes "#VALUE!"
Get rid of the errors in the cells being subtotalled.
Trap for it in your formulas. Post an example of two of the cells that return #VALUE! that are causing the error in the Subtotal. Gord Dibben MS Excel MVP On Fri, 30 Jan 2009 12:09:01 -0800, JAbels001 wrote: |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal function 1 that excludes "#VALUE!"
You must be getting #N/A from your formula...
Wrap your formula in a IF and ISNA like this =IF(ISNA(your formula here),0,your formula here)) If you don't want 0 you can replace it with "" (2 double quotes) "JAbels001" wrote: Sorry, I was trying to reply and describe the problem but it wouldnt let me until someone responded. I'm trying to calculate a subtotal formula b/c the data I'm using can be filtered and I want it to update whenever it is changed. The problem is that some of the values are "#VALUE!" which results in the subtotal formula coming back as #VALUE!. Please help... "Sheeloo" wrote: This happens when there is no text in the body (as in this post). We have been requesting all users to describe the problem in the text of the post but not everyone does that... :-( "Shane Devenshire" wrote: Please place the question in the body of the email, all I can read with my browser is the following when I hit reply: "An error occurred while sending your post -------------------------------------------------------------------------------- We're sorry, but there was a problem with the system and your post was not received. The error has been reported to Operations and will be investigated as soon as possible. Please try again later." -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JAbels001" wrote in message ... |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal function 1 that excludes "#VALUE!"
Unfortunately that is a harder task than getting the subtotal to exclude
them. Is there anyway to have a cell that only shows the value of another cell just what is in that cell? For example on my one tab I have a formula that reads "=April!I1", however if I1 on tab "April" is blank then it shows "0" not blank. I would like it to show the true contents. "Gord Dibben" wrote: Get rid of the errors in the cells being subtotalled. Trap for it in your formulas. Post an example of two of the cells that return #VALUE! that are causing the error in the Subtotal. Gord Dibben MS Excel MVP On Fri, 30 Jan 2009 12:09:01 -0800, JAbels001 wrote: |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal function 1 that excludes "#VALUE!"
Use
=IF(April!I1="","",April!I1) "JAbels001" wrote: Unfortunately that is a harder task than getting the subtotal to exclude them. Is there anyway to have a cell that only shows the value of another cell just what is in that cell? For example on my one tab I have a formula that reads "=April!I1", however if I1 on tab "April" is blank then it shows "0" not blank. I would like it to show the true contents. "Gord Dibben" wrote: Get rid of the errors in the cells being subtotalled. Trap for it in your formulas. Post an example of two of the cells that return #VALUE! that are causing the error in the Subtotal. Gord Dibben MS Excel MVP On Fri, 30 Jan 2009 12:09:01 -0800, JAbels001 wrote: |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal function 1 that excludes "#VALUE!"
That'll work, shouldve thought of it, thanks!
"Sheeloo" wrote: Use =IF(April!I1="","",April!I1) "JAbels001" wrote: Unfortunately that is a harder task than getting the subtotal to exclude them. Is there anyway to have a cell that only shows the value of another cell just what is in that cell? For example on my one tab I have a formula that reads "=April!I1", however if I1 on tab "April" is blank then it shows "0" not blank. I would like it to show the true contents. "Gord Dibben" wrote: Get rid of the errors in the cells being subtotalled. Trap for it in your formulas. Post an example of two of the cells that return #VALUE! that are causing the error in the Subtotal. Gord Dibben MS Excel MVP On Fri, 30 Jan 2009 12:09:01 -0800, JAbels001 wrote: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text "comparison" operator for "contains" used in an "IF" Function | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Cannot Reference "Array" in Subtotal Function | Excel Worksheet Functions |