| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Help with "SUM" AND "ABS" in same formula
The cells in a SUM formula all include formulas that provide the cells with
negative numbers, positive numbers and blank cells. Need to have the respective values to be used in another formula as absolute values. Need to have the negative values in the range to stay displayed as negatives and the blank cells in the range to stay displayed as blank. Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". Is there something to add to resolve the error message? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Help with "SUM" AND "ABS" in same formula
"Randy" wrote:
Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". You need to enter =SUM(ABS(range)) as an array formula. That is, press with ctrl-shift-Enter instead of just Enter. At this point, you can select the cell, press F2, then press ctrl-shift-Enter. Alternatively, use the formula =SUMPRODUCT(ABS(range)), which is not an array formula; simply press Enter. ----- original message ----- "Randy" wrote in message ... The cells in a SUM formula all include formulas that provide the cells with negative numbers, positive numbers and blank cells. Need to have the respective values to be used in another formula as absolute values. Need to have the negative values in the range to stay displayed as negatives and the blank cells in the range to stay displayed as blank. Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". Is there something to add to resolve the error message? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Help with "SUM" AND "ABS" in same formula
Hi Randy,
ISNA OR ISERR will help, could you post your formula. thanks "Randy" wrote: The cells in a SUM formula all include formulas that provide the cells with negative numbers, positive numbers and blank cells. Need to have the respective values to be used in another formula as absolute values. Need to have the negative values in the range to stay displayed as negatives and the blank cells in the range to stay displayed as blank. Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". Is there something to add to resolve the error message? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Help with "SUM" AND "ABS" in same formula
PS....
"Randy" wrote: Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". Hmm.... There might be something wrong with my understanding of your problem. SUM(ABS(range)) should __always__ return #VALUE unless you enter the formula as an array formula. And if you enter an array formula, SUM(ABS(range)) should __always__ work regardless of the presence of empty cells in the range. But if the "blank cells" contain text, notably "", you will need the following array formula (again, commit with ctrl-shift-Enter, not just Enter): =SUM(IF(range<"",ABS(range))) Also note: the SUMPRODUCT alternative will not work as a non-array formula. ----- original message ----- "JoeU2004" wrote in message ... "Randy" wrote: Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". You need to enter =SUM(ABS(range)) as an array formula. That is, press with ctrl-shift-Enter instead of just Enter. At this point, you can select the cell, press F2, then press ctrl-shift-Enter. Alternatively, use the formula =SUMPRODUCT(ABS(range)), which is not an array formula; simply press Enter. ----- original message ----- "Randy" wrote in message ... The cells in a SUM formula all include formulas that provide the cells with negative numbers, positive numbers and blank cells. Need to have the respective values to be used in another formula as absolute values. Need to have the negative values in the range to stay displayed as negatives and the blank cells in the range to stay displayed as blank. Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". Is there something to add to resolve the error message? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Help with "SUM" AND "ABS" in same formula
Opps, I didn't realize you posted your formula, try
=SUMPRODUCT(ABS(C11:C14)) change c11:c14 for your range "Eduardo" wrote: Hi Randy, ISNA OR ISERR will help, could you post your formula. thanks "Randy" wrote: The cells in a SUM formula all include formulas that provide the cells with negative numbers, positive numbers and blank cells. Need to have the respective values to be used in another formula as absolute values. Need to have the negative values in the range to stay displayed as negatives and the blank cells in the range to stay displayed as blank. Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". Is there something to add to resolve the error message? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Help with "SUM" AND "ABS" in same formula
Try this array formula** :
=SUM(IF(ISNUMBER(A1:A10),ABS(A1:A10))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Randy" wrote in message ... The cells in a SUM formula all include formulas that provide the cells with negative numbers, positive numbers and blank cells. Need to have the respective values to be used in another formula as absolute values. Need to have the negative values in the range to stay displayed as negatives and the blank cells in the range to stay displayed as blank. Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". Is there something to add to resolve the error message? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Help with "SUM" AND "ABS" in same formula
How about a couple of *non* array formulas:
=SUMIF(A1:A10,"0")-SUMIF(A1:A10,"<0") =SUM(ABS(SUMIF(A1:A10,{"0","<0"}))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Randy" wrote in message ... The cells in a SUM formula all include formulas that provide the cells with negative numbers, positive numbers and blank cells. Need to have the respective values to be used in another formula as absolute values. Need to have the negative values in the range to stay displayed as negatives and the blank cells in the range to stay displayed as blank. Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". Is there something to add to resolve the error message? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Help with "SUM" AND "ABS" in same formula
"JoeU200"
Thank You, the =SUM(IF(range<"",ABS(range))) works fine. "JoeU2004" wrote: PS.... "Randy" wrote: Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". Hmm.... There might be something wrong with my understanding of your problem. SUM(ABS(range)) should __always__ return #VALUE unless you enter the formula as an array formula. And if you enter an array formula, SUM(ABS(range)) should __always__ work regardless of the presence of empty cells in the range. But if the "blank cells" contain text, notably "", you will need the following array formula (again, commit with ctrl-shift-Enter, not just Enter): =SUM(IF(range<"",ABS(range))) Also note: the SUMPRODUCT alternative will not work as a non-array formula. ----- original message ----- "JoeU2004" wrote in message ... "Randy" wrote: Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". You need to enter =SUM(ABS(range)) as an array formula. That is, press with ctrl-shift-Enter instead of just Enter. At this point, you can select the cell, press F2, then press ctrl-shift-Enter. Alternatively, use the formula =SUMPRODUCT(ABS(range)), which is not an array formula; simply press Enter. ----- original message ----- "Randy" wrote in message ... The cells in a SUM formula all include formulas that provide the cells with negative numbers, positive numbers and blank cells. Need to have the respective values to be used in another formula as absolute values. Need to have the negative values in the range to stay displayed as negatives and the blank cells in the range to stay displayed as blank. Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". Is there something to add to resolve the error message? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Help with "SUM" AND "ABS" in same formula
"T. Valko"
-Thank You, the formula works fine. "T. Valko" wrote: Try this array formula** : =SUM(IF(ISNUMBER(A1:A10),ABS(A1:A10))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Randy" wrote in message ... The cells in a SUM formula all include formulas that provide the cells with negative numbers, positive numbers and blank cells. Need to have the respective values to be used in another formula as absolute values. Need to have the negative values in the range to stay displayed as negatives and the blank cells in the range to stay displayed as blank. Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". Is there something to add to resolve the error message? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Help with "SUM" AND "ABS" in same formula
"RagDyer"
-Thank You, they both work. "RagDyer" wrote: How about a couple of *non* array formulas: =SUMIF(A1:A10,"0")-SUMIF(A1:A10,"<0") =SUM(ABS(SUMIF(A1:A10,{"0","<0"}))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Randy" wrote in message ... The cells in a SUM formula all include formulas that provide the cells with negative numbers, positive numbers and blank cells. Need to have the respective values to be used in another formula as absolute values. Need to have the negative values in the range to stay displayed as negatives and the blank cells in the range to stay displayed as blank. Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". Is there something to add to resolve the error message? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Help with "SUM" AND "ABS" in same formula
"RagDyer"
-Thank You, they both work fine. "RagDyer" wrote: How about a couple of *non* array formulas: =SUMIF(A1:A10,"0")-SUMIF(A1:A10,"<0") =SUM(ABS(SUMIF(A1:A10,{"0","<0"}))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Randy" wrote in message ... The cells in a SUM formula all include formulas that provide the cells with negative numbers, positive numbers and blank cells. Need to have the respective values to be used in another formula as absolute values. Need to have the negative values in the range to stay displayed as negatives and the blank cells in the range to stay displayed as blank. Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". Is there something to add to resolve the error message? |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Help with "SUM" AND "ABS" in same formula
You're welcome, and thanks for the feed-back.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Randy" wrote in message ... "RagDyer" -Thank You, they both work fine. "RagDyer" wrote: How about a couple of *non* array formulas: =SUMIF(A1:A10,"0")-SUMIF(A1:A10,"<0") =SUM(ABS(SUMIF(A1:A10,{"0","<0"}))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Randy" wrote in message ... The cells in a SUM formula all include formulas that provide the cells with negative numbers, positive numbers and blank cells. Need to have the respective values to be used in another formula as absolute values. Need to have the negative values in the range to stay displayed as negatives and the blank cells in the range to stay displayed as blank. Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". Is there something to add to resolve the error message? |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need Help with "SUM" AND "ABS" in same formula
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Randy" wrote in message ... "T. Valko" -Thank You, the formula works fine. "T. Valko" wrote: Try this array formula** : =SUM(IF(ISNUMBER(A1:A10),ABS(A1:A10))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Randy" wrote in message ... The cells in a SUM formula all include formulas that provide the cells with negative numbers, positive numbers and blank cells. Need to have the respective values to be used in another formula as absolute values. Need to have the negative values in the range to stay displayed as negatives and the blank cells in the range to stay displayed as blank. Tried using =sum(abs(range)), which works fine if the blank cells are omitted from the range. If the blank cells are part of the range the result is "#VALUE!". Is there something to add to resolve the error message? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| correct syntax for nesting "if", "and", and "vlookup"....if possib | 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 | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Linear Mode
