| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
average with max
I have a table of numbers, some cells are blank, and I'd like to calculate
the average (excluding blanks). Easy so far... However some of the numbers are "spikes", and rather than discarding these, I'd like to set a value for them, to be included in the average. I could have a second table with an IF function replacing any numbers too high, but would prefer not to if possible Any help much appreciated Thanks heaps -Bryce |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average with max
One possibility:
(Matrix-Formula, ctrl+shift+enter to apply) =SUMPRODUCT(IF((A1:A23)B25,B25,A1:A23))/SUMPRODUCT(((A1:A23)0)*1) where A1:A23 is your range of data B25 is your spike value hth Carlo On Jan 16, 2:24*pm, Bryce wrote: I have a table of numbers, some cells are blank, and I'd like to calculate the average (excluding blanks). *Easy so far... However some of the numbers are "spikes", and rather than discarding these, I'd like to set a value for them, to be included in the average. I could have a second table with an IF function replacing any numbers too high, but would prefer not to if possible Any help much appreciated Thanks heaps -Bryce |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average with max
Excellent, worked perfectly
Thanks heaps -Bryce "carlo" wrote: One possibility: (Matrix-Formula, ctrl+shift+enter to apply) =SUMPRODUCT(IF((A1:A23)B25,B25,A1:A23))/SUMPRODUCT(((A1:A23)0)*1) where A1:A23 is your range of data B25 is your spike value hth Carlo On Jan 16, 2:24 pm, Bryce wrote: I have a table of numbers, some cells are blank, and I'd like to calculate the average (excluding blanks). Easy so far... However some of the numbers are "spikes", and rather than discarding these, I'd like to set a value for them, to be included in the average. I could have a second table with an IF function replacing any numbers too high, but would prefer not to if possible Any help much appreciated Thanks heaps -Bryce |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average with max
Hi Bryce,
This probably won't do it but if it will then it's a nice solution - use TRIMMEAN If this doesn't meet your needs, then we need to know how you decide a number is too large or too small and what value you want to substitute in these cases. Further do you want to actually replace the spikes or have a new range that shows the adjusted values but retain the original data? -- Cheers, Shane Devenshire "Bryce" wrote: I have a table of numbers, some cells are blank, and I'd like to calculate the average (excluding blanks). Easy so far... However some of the numbers are "spikes", and rather than discarding these, I'd like to set a value for them, to be included in the average. I could have a second table with an IF function replacing any numbers too high, but would prefer not to if possible Any help much appreciated Thanks heaps -Bryce |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average with max
Hi Shane
Carlo's formula seemed to work fine. Just to clarify: I had a couple of tabs for various projects showing a table each of numbers. I then had a summary tab listing the projects, the average, and now average with spikes capped. The cap value is now a cell on the summary page which allows me to play with it, leaving the original data. Thanks heaps -Bryce "ShaneDevenshire" wrote: Hi Bryce, This probably won't do it but if it will then it's a nice solution - use TRIMMEAN If this doesn't meet your needs, then we need to know how you decide a number is too large or too small and what value you want to substitute in these cases. Further do you want to actually replace the spikes or have a new range that shows the adjusted values but retain the original data? -- Cheers, Shane Devenshire "Bryce" wrote: I have a table of numbers, some cells are blank, and I'd like to calculate the average (excluding blanks). Easy so far... However some of the numbers are "spikes", and rather than discarding these, I'd like to set a value for them, to be included in the average. I could have a second table with an IF function replacing any numbers too high, but would prefer not to if possible Any help much appreciated Thanks heaps -Bryce |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average with max
Assumptions:
Spikes are any value =100. Spikes should be "capped" at 100. C1 = spike value = 100 A1:A10 = numbers to average Try this array formula** : =AVERAGE(IF(ISNUMBER(A1:A10),IF(A1:A10=C1,C1,A1:A 10))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bryce" wrote in message ... I have a table of numbers, some cells are blank, and I'd like to calculate the average (excluding blanks). Easy so far... However some of the numbers are "spikes", and rather than discarding these, I'd like to set a value for them, to be included in the average. I could have a second table with an IF function replacing any numbers too high, but would prefer not to if possible Any help much appreciated Thanks heaps -Bryce |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average with max
Thanks, now I'll have to decide which formula looks nicer :-)
-Bryce "T. Valko" wrote: Assumptions: Spikes are any value =100. Spikes should be "capped" at 100. C1 = spike value = 100 A1:A10 = numbers to average Try this array formula** : =AVERAGE(IF(ISNUMBER(A1:A10),IF(A1:A10=C1,C1,A1:A 10))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bryce" wrote in message ... I have a table of numbers, some cells are blank, and I'd like to calculate the average (excluding blanks). Easy so far... However some of the numbers are "spikes", and rather than discarding these, I'd like to set a value for them, to be included in the average. I could have a second table with an IF function replacing any numbers too high, but would prefer not to if possible Any help much appreciated Thanks heaps -Bryce |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average with max
You're welcome!
-- Biff Microsoft Excel MVP "Bryce" wrote in message ... Thanks, now I'll have to decide which formula looks nicer :-) -Bryce "T. Valko" wrote: Assumptions: Spikes are any value =100. Spikes should be "capped" at 100. C1 = spike value = 100 A1:A10 = numbers to average Try this array formula** : =AVERAGE(IF(ISNUMBER(A1:A10),IF(A1:A10=C1,C1,A1:A 10))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bryce" wrote in message ... I have a table of numbers, some cells are blank, and I'd like to calculate the average (excluding blanks). Easy so far... However some of the numbers are "spikes", and rather than discarding these, I'd like to set a value for them, to be included in the average. I could have a second table with an IF function replacing any numbers too high, but would prefer not to if possible Any help much appreciated Thanks heaps -Bryce |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average with max
Your welcome, thanks for the feedback
Carlo On Jan 17, 3:17*am, "T. Valko" wrote: You're welcome! -- Biff Microsoft Excel MVP "Bryce" wrote in message ... Thanks, now I'll have to decide which formula looks nicer :-) -Bryce "T. Valko" wrote: Assumptions: Spikes are any value =100. Spikes should be "capped" at 100. C1 = spike value = 100 A1:A10 = numbers to average Try this array formula** : =AVERAGE(IF(ISNUMBER(A1:A10),IF(A1:A10=C1,C1,A1:A 10))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bryce" wrote in message ... I have a table of numbers, some cells are blank, and I'd like to calculate the average (excluding blanks). *Easy so far... However some of the numbers are "spikes", and rather than discarding these, I'd like to set a value for them, to be included in the average. I could have a second table with an IF function replacing any numbers too high, but would prefer not to if possible Any help much appreciated Thanks heaps -Bryce- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
| average cells, show 0 if nothing to average | Excel Discussion (Misc queries) | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
| Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
| how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions | |||
Hybrid Mode
