filtering numbers?

**NBVC** · 04-01-2010, 08:54 AM

I think I am dense to this one...

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

**Alex G** · 04-01-2010, 12:31 PM

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

Alex G

**NBVC** · 04-01-2010, 12:59 PM

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** · 04-01-2010, 04:25 PM

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