# Averaging both positive and negative numbers

• On my data sheet from col D through col Z is where the data is kept. The numbers in in col D through col Z, can be both negative and positive numbers, so what I want to do is average, the last three numbers in the range for a three day average, and the last 5 days for a average, and the last ten. There will be zeros when no information is required. Also not all people will have a 10,5,3 day information for a average number.

Example:
col D row 3: 65.02
col E row 3: -2.03
col F row 3: .00
col H row 3: 15.00
col I row 3: .00
col J row 3: -.23

So the three day average should show 4.246. But when I use the average formula the result shows 2.548. So what type of formula would I need to get the correct results.

• Re: Averaging both positive and negative numbers

Please explain the mathematics that gets you to arrive at 2.548.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Re: Averaging both positive and negative numbers

Ok the way the 2.548 was figured, was really very simple. I just dragged the cursor across from cell E3 to cell J3 and it gave me the number of 2.548, saw answer at the bottom right hand corner of worksheet.

Now the 4.246 was calculated by me selecting each cell individually. So select cell E3,hold the ctrl key down, select cell H3, hold the ctrl key again, and lastly select cell J3. Gave me my answer of 4.246,again showed at the bottom right hand corner of the worksheet.

• Re: Averaging both positive and negative numbers

I see, thanks. Try this:

=AVERAGEIF(E3:I3,"<>"&0,E3:I3)

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Re: Averaging both positive and negative numbers

But doesn't that just average the complete row? I trying to come up with a formula that starting at farthest right cell in a row in this case cell Z3 to D3 find the last three numbers, or the last 5 numbers etc?

• Re: Averaging both positive and negative numbers

Hello,

If you need the last used non-empty column ... you can test following:

Code
``=ADDRESS(3,SUMPRODUCT(MAX((COLUMN(A3:AZ3))*(A3:AZ3<>""))),4)``

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Re: Averaging both positive and negative numbers

Thanks for the reply, but I'm sorry but I do not understand this formula, isn't this just going to give me the larger of the numbers in the row. What I'm needing, is to find the last three numbers in that row and then average them and so forth.

• Re: Averaging both positive and negative numbers

Hello again,

Attached is your sample workbook ... :wink:

Let me know if this is in line with your expectations ...

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Re: Averaging both positive and negative numbers

Thank you, Thank you that is it. Wow long formula, if I was in your area I would give a very big hug, because I have tried for days to figure this one problem out, and you did it.

• Re: Averaging both positive and negative numbers

Quote from caldog;794151

Thank you, Thank you that is it. Wow long formula, if I was in your area I would give a very big hug, because I have tried for days to figure this one problem out, and you did it.