Find an unespected data item in a row

Hi,

Are you saying that you have a list comprising those 4 numbers only and you want to exclude the 8.2 values in your average?

If so one way:

As an array formula i.e. entered with Ctrl-Shift-Enter

=SUM((D1:D100<>8.2)*(D1:D100))/COUNTIF(D1:D100,"<>8.2")

Hi

You could try something like
{=AVERAGE(IF(A2:D2<=2*STDEV(A2:D2),A2:D2,""))}
Note that this is an array formula which must be entered or amended using Control+Shift+Enter (CSE) not just Enter.
When you use CSE, Excel will place curly braces { } around your formula. Do not type them yourself.

Basically this formula is only taking values that are within 2 standard deviations of the mean of the values.
You will have to play about with the 2 to find a level which suits your data.

Alternatively, take a look at the TRIMMEAN function.

Are you always this rude?

Originally Posted by Imran688

This is exactly not what i am looking for and i didnt asked for how to sum.
The fourth value is sometimes unexpected. for example look at this set 3,4,6,19. Now 19 was very much unexpected. How to find out that there is an unexpected value in the fourth cell?

The formula I gave you didn't show you how to sum, it showed you how to average. Which you'd understand if you had actually understood the formula.


Since your post was by no means clear I specifically asked if what I was proposing was what you meant. All it required was a clarification not a bald statement that it was wrong. Defining what YOU mean by 'unexpected' would have been a start.

You will do yourself no favours if you continue with this approach.

The question may be very simple but you don't give any guidance as to why the fourth value is good or bad. How do you define "what it should be". Only you know that. We're not psychic - well I'm not.

State the rules that determine why 6 or 7 is OK, but 8 is not. What about 7.1, 7.2, 7.3 etc. Are they good or bad and why.

And don't expect answers if you continue to be rude or curt. A sorry would not go amiss.

Hi
Have you looked at the solution I posted a while ago?

This is rather like how long is a piece of string. Only you can decide what might be competitive in your market but also maximise your margin.

One strategy might for instance be to take the minimum of -
1. The average of the first three values x 1.5 (as an example)
2. The fourth value

So if the values were say 8,9,12 & 13, the average of the first three x 1.5 is 14.5
So the minimum of 14.5 and the fourth value is 13

Just one method. Whether your market is capable of such modelling is of course open to question. Walking the market to get a feeling for what sells and why is probably going to be much more use.