| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing a row excluding a text character
Howdy...
Here's my question... I'm attempting to sum a row of #'s MINUS the lowest value. In my spreadsheet the lowest value could be a text character (NP) OR it could be a numeral. EX: (#'s are in seperate cells) 4 8 9 10 NP 2 = 33 (doesn't include the NP) 2 4 6 8 1 = 20 (doesn't include the 1) I've used the formula sum(a2:a10) - min (a2:a10) but, if NP is in the array, the value returned is 0. Any help would be appreciated. THX |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summing a row excluding a text character
Maybe something like this?:
With your sample data in A1:F2 G1: =SUM(A1:F1)-IF(COUNTIF(A1:F1,"NP"),0,MIN(A1:F1)) Copy that formula down to G2 Does that help? *********** Regards, Ron XL2002, WinXP "Chris" wrote: Howdy... Here's my question... I'm attempting to sum a row of #'s MINUS the lowest value. In my spreadsheet the lowest value could be a text character (NP) OR it could be a numeral. EX: (#'s are in seperate cells) 4 8 9 10 NP 2 = 33 (doesn't include the NP) 2 4 6 8 1 = 20 (doesn't include the 1) I've used the formula sum(a2:a10) - min (a2:a10) but, if NP is in the array, the value returned is 0. Any help would be appreciated. THX |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summing a row excluding a text character
Thanks Ron...that worked great.
Regards... "Ron Coderre" wrote: Maybe something like this?: With your sample data in A1:F2 G1: =SUM(A1:F1)-IF(COUNTIF(A1:F1,"NP"),0,MIN(A1:F1)) Copy that formula down to G2 Does that help? *********** Regards, Ron XL2002, WinXP "Chris" wrote: Howdy... Here's my question... I'm attempting to sum a row of #'s MINUS the lowest value. In my spreadsheet the lowest value could be a text character (NP) OR it could be a numeral. EX: (#'s are in seperate cells) 4 8 9 10 NP 2 = 33 (doesn't include the NP) 2 4 6 8 1 = 20 (doesn't include the 1) I've used the formula sum(a2:a10) - min (a2:a10) but, if NP is in the array, the value returned is 0. Any help would be appreciated. THX |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Text entries behaving like numbers | Excel Discussion (Misc queries) | |||
| Find last occurance of character in text string | Excel Worksheet Functions | |||
| Remove last character of text string | Excel Worksheet Functions | |||
| Changing a specific character type in text string | Excel Discussion (Misc queries) | |||
| Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Linear Mode
