filtering numbers?

[Alex G]

Hello
I've been trying to find a formula that I need but am unable to. Can anyone help?

There are two columns A & B. In A there is a sum of positive & negative numbers. B contains an average of positive numbers, present value 20. In a 3rd column I need a formula based on column A that includes all numbers that are the average result of colomn B or lower but excludes ones higher than the column B result.

The manually worked out answer should be 16.60.

Any ideas.

Alex G

[NBVC]

I think we need to see a better example of actual data and expected results.. at least I think I do.

[NBVC]

I only noticed that you added a workbook after seeing your rep points comment. You should reply next time and add attachment, this way it will go back to top of my list and I will know you added some information....

.. anyway, still a little confused. You did not show expected result.

Are you looking for perhaps:

=AVERAGEIF(A5:A113,"<="&B4,B5:B113)

[Alex G]

I only noticed that you added a workbook after seeing your rep points comment. You should reply next time and add attachment, this way it will go back to top of my list and I will know you added some information....

.. anyway, still a little confused. You did not show expected result.

Are you looking for perhaps:

=AVERAGEIF(A5:A113,"<="&B4,B5:B113)

Hi NBVC
Think of the average (20) in column B as a sell order. Any market that reaches that number will trigger a sell signal. A formula that filters out figures higher than the column B average but includes the lower & negative numbers should work. So, a max value of 20 (B average) and all bellow needs to be entered.

Alex G

[Alex G]

Hi NBVC
Think of the average (20) in column B as a sell order. Any market that reaches that number will trigger a sell signal. A formula that filters out figures higher than the column B average but includes the lower & negative numbers should work. So, a max value of 20 (B average) and all bellow needs to be entered.

Alex G

Tried the formula NBVC, didn't seem to work. Manually, the result worked out at 16.60.

Alex G

[Alex G]

NBVC
Thanks for your formula but it didn't work. I've tried to explain it more clearly (with my limited knowledge of excel)

[NBVC]

I think I am dense to this one...

Can you please tell me exactly how you would arrive at 16.60?

[Alex G]

NBVC
I've tried to make the attatchment a little clearer.

Alex G

[NBVC]

Note that your result in B4 is not actually 20. It is actually 20.38888889

So not sure if you wanted to use the actual result or the rounded resulted.

If you want to use actual result then:

=SUMIF(A5:A113,"<="&B4)+COUNTIF(A5:A113,">"&B4)*B4

gives 17.38

If you want to use the rounded result, then:

=SUMIF(A5:A113,"<="&ROUND(B4,0))+COUNTIF(A5:A113,">"&ROUND(B4,0))*ROUND(B4,0)

gives 16.60

Is that what you were looking for?

[Alex G]

NBVC
Thanks for the formula. It works fine for the columns I attatched in the earlier post but then I copied & pasted it to a different spread sheet, changed the data ranges and manually checked the result. For some reason it came out incorrect. Have I made a mistake during the movement of the formula?

=SUMIF(B79:B114,"<="&ROUND(C5,0))+COUNTIF(B79:B114,">"&ROUND(C5,0))*ROUND(C5,0)

For simplicity the rounded version is fine.


Alex G

[NBVC]

It looks fine.. but without seeing the data, don't know for sure..

The logic of the formula is:

Sum all values in B79 to B114 where those values are less than or equal to the rounded result found in C5... then add the number of values in B79 to B114 where they are greater than the rounded result in C5 multiplied by the rounded result of C5 (essentially this replaces all values greater than C5 with the C5 value and adds them along with the values that are less than or equal to C5).

Is that logic correct or not?

Maybe it should be:

=SUMIF(B79:B114,"<"&ROUND(C5,0))+COUNTIF(B79:B114,">="&ROUND(C5,0))*ROUND(C5,0)

[Alex G]

Or a slight change - sum all values in B79 to B114 where those values are less than the rounded result in C5. Then add the number of values in B79 to B114 where they are equal or greater than the rounded result in C5. This may not inprove but what do you think? Your logic sounds correct but I'm still geting a wrong result when compared with a calculator.
I've attached another example showing each calculation done with a calculator. This time with the correct result of 18.7 not 16.6 as previously shown - apologies.

[NBVC]

Your suggestion is the formula in my last post..

Looks like you forgot to include -2.10 from A29 in your manual calcs...

I used:

=SUMIF(A5:A32,"<"&ROUND(B4,0))+COUNTIF(A5:A32,">="&ROUND(B4,0))*ROUND(B4,0)

and got 16.60 which checks out with manual calcs.

[Alex G]

I've put your formula in A2. Have I made a mistake with the data ranges (changed them to suit columns). The Autosum total of C is 21.8. Must be something I'm doing when moving the formula?

[NBVC]

For some reason you applied 10 to C5 and C25 when the corresponding values in A5 and A25 are less than 10. When I change them to what they should be (2.70 and 7.50, resectively) then the manual math is 12 as per my formula result.

[Alex G]

Should have explained that more clearly. The B column represents the maximum a particular market moves away from my trigger. Hence 10 in C5 & C25. My theory is to catch a bigger chunk of these hi's & low's based upon the average in B4. C will always contain positive numbers because they represent a movement away from a given point. A represents the point profit or loss once a trigger has been activated Can your formula catch that kind of substitution?

[Alex G]

Oops - See #16.

[NBVC]

This is my last attempt as we are not on the same wavelength....

Try:

Please Login or Register  to view this content.

[Alex G]

NBVC
Fantastic. This one works. I've tested it then compared with a calculator - all is good.
I've obvoiusly tested your patience. Much appreciated. Thank you very much.

Alex G