How Do I Create an Absolute Value Running Total

zotah

Board Regular
Joined
Feb 1, 2014
Messages
89
How Do I Create an Absolute Value Running Total or that's what I think it's called.

Let's say I have a column of numbers like this

5
2
8
-9
-5
-15
4
8
7
-6
-4
-2
-8
5
-4
7

and I want to have a running total in the cell directly to the right of these numbers that did a running total average of each number where the formula will figure out that it will translate all the negative (-) numbers are to be read as positive numbers and formulating it's average so all the negative number will be counted as a positive number like this

5
2
8
9
5
15
4
8
7
6
4
2
8
5
4
7

then the formula will give a running average of the first 2 cells of the numbers 5 and 2 and then the next cell to the left that says 5 and 2 and 8 will give the running total of those 3 numbers written in the cell directly to the right of each number listed. and then the average will be given of the absolute numbers will list the average of the first 4 numbers listed of 5 and 2 and 8 and 9 etc.

This formula should be able to be dragged down the excel page so it wouldn't matter how long the list of number were it will give the absolute running total numbers all the way down.

I'm not that good in understanding the math side of these problems. So I'm sure I'm not explaining to the right way. But I hope you understand what I mean.

So

Number 1) All the numbers listed in Cell A will be read in the formula Cell B as positive numbers only.

Number 2) All the averages are in a running total down Cell B and the averages are based on each number combination of the total numbers above it only.

Example

Cell 1) 5 (Taking the average of the number 5 only = 5)
Cell 2) 2 (Taking the average of both the number 5 and the number 3 =)
Cell 3) 8 (Taking the average of all 3 numbers of the 5 and 3 and 8)
Cell 4) 9 (Taking the average of all 4 numbers of the 5 and 3 and 8 and 9)
Cell 5) 5 (Taking the average of all 5 numbers of the 5 and 3 and 8 and 9 and 5) Etc. Etc.

I hope I explained what I'm trying to do correctly.

Thanks for any help you can give me on this to create the formula I need,

Zotah
 
I must be missing something
5 = 5
5 + 2 / 2 = 3.5
5 + 3 + 8 / 3 = 5
 
Upvote 0
Assumptions:
1) Your data starts with cell A1 and follows down the same column A
2) Your data is only NUMERIC and no text or (dash/hyphen) is used to denote no entry. (means you can use ZERo or --> 0 <-- or a positive or negative number only

In B1 (considering your values start from A1) put this -----> =IF(A1<0,A1*-1,A1)
In B2 put this -------------------------------------------> =IF(A2<0,(A2*-1)+(B1*COUNT($A$1:A1)),A2+(B1*COUNT($A$1:A1)))/COUNT($A$1:A2)


you can drag this as much u like it will work.

IMPORTANT POINT:
1) DO NOT drag the first cell. It is just a value used by the cell below to get the formula going. However, you can drag the second cell as far as you want.

Hope this is what you wanted, Test and inform plz.
Thanks
Gud Luck
 
Upvote 0
Assumptions:
1) Your data starts with cell A1 and follows down the same column A
2) Your data is only NUMERIC and no text or (dash/hyphen) is used to denote no entry. (means you can use ZERo or --> 0 <-- or a positive or negative number only

In B1 (considering your values start from A1) put this -----> =IF(A1<0,A1*-1,A1)
In B2 put this -------------------------------------------> =IF(A2<0,(A2*-1)+(B1*COUNT($A$1:A1)),A2+(B1*COUNT($A$1:A1)))/COUNT($A$1:A2)


you can drag this as much u like it will work.

IMPORTANT POINT:
1) DO NOT drag the first cell. It is just a value used by the cell below to get the formula going. However, you can drag the second cell as far as you want.

Hope this is what you wanted, Test and inform plz.
Thanks
Gud Luck


Tested and approved,

Your formula worked perfectly,

Thanks so much, Your the greatest......

Zotah
 
Upvote 0
Your Welcome :)

Sorry to bother you again but I'm having a little problem

I translated your formula into the cell "Q" that I need for the first column of figures and it works great

=IF(Q2<0,Q2*-1,Q2)
In B2 put this -------------------------------------------> =IF(Q3<0,(Q3*-1)+(U2*COUNT($Q$2:Q2)),Q3+(U2*COUNT($Q$2:Q2)))/COUNT($Q$2:Q3)

However when I copy the formula over to the next set of cells in the same line, it's not giving me the right answers.

Can you tell me what I'm doing wrong.

Here's the first cell in (Q) that's working great.

[TABLE="width: 563"]
<colgroup><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Cell Q[/TD]
[TD]Cell U[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD]0[/TD]
[TD]0.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD]0[/TD]
[TD]0.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]-1[/TD]
[TD]0.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 5[/TD]
[TD]3[/TD]
[TD]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 6[/TD]
[TD]-1[/TD]
[TD]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 7[/TD]
[TD]-3[/TD]
[TD]1.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 8[/TD]
[TD]2[/TD]
[TD]1.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 9[/TD]
[TD]2[/TD]
[TD]1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 10[/TD]
[TD]-2[/TD]
[TD]1.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 11[/TD]
[TD]4[/TD]
[TD]1.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 12[/TD]
[TD]-2[/TD]
[TD]1.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 13[/TD]
[TD]0[/TD]
[TD]1.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 14[/TD]
[TD]-2[/TD]
[TD]1.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 15[/TD]
[TD]-1[/TD]
[TD]1.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 16[/TD]
[TD]-1[/TD]
[TD]1.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 17[/TD]
[TD]3[/TD]
[TD]1.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 18[/TD]
[TD]-1[/TD]
[TD]1.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 19[/TD]
[TD]-3[/TD]
[TD]1.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 20[/TD]
[TD]2[/TD]
[TD]1.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 21[/TD]
[TD]-2[/TD]
[TD]1.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 22[/TD]
[TD]4[/TD]
[TD]1.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 23[/TD]
[TD]-2[/TD]
[TD]1.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 24[/TD]
[TD]0[/TD]
[TD]1.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 25[/TD]
[TD]-2[/TD]
[TD]1.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 26[/TD]
[TD]-1[/TD]
[TD]1.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 27[/TD]
[TD]-4[/TD]
[TD]1.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 28[/TD]
[TD]2[/TD]
[TD]1.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 29[/TD]
[TD]-1[/TD]
[TD]1.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 30[/TD]
[TD]7[/TD]
[TD]2.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 31[/TD]
[TD]2[/TD]
[TD]2.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 32[/TD]
[TD]-1[/TD]
[TD]2.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 33[/TD]
[TD]-4[/TD]
[TD]2.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 34[/TD]
[TD]-2[/TD]
[TD]2.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 35[/TD]
[TD]-8[/TD]
[TD]2.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 36[/TD]
[TD]11[/TD]
[TD]2.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 37[/TD]
[TD]10[/TD]
[TD]2.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 38[/TD]
[TD]2[/TD]
[TD]2.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 39[/TD]
[TD]-2[/TD]
[TD]2.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 40[/TD]
[TD]2[/TD]
[TD]2.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 41[/TD]
[TD]3[/TD]
[TD]2.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 42[/TD]
[TD]1[/TD]
[TD]2.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 43[/TD]
[TD]-2[/TD]
[TD]2.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 44[/TD]
[TD]2[/TD]
[TD]2.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 45[/TD]
[TD]-3[/TD]
[TD]2.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 46[/TD]
[TD]-2[/TD]
[TD]2.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 47[/TD]
[TD]-5[/TD]
[TD]2.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 48[/TD]
[TD]-8[/TD]
[TD]2.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 49[/TD]
[TD]11[/TD]
[TD]2.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 50[/TD]
[TD]10[/TD]
[TD]3.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 51[/TD]
[TD]2[/TD]
[TD]3.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 52[/TD]
[TD]2[/TD]
[TD]3.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 53[/TD]
[TD]2[/TD]
[TD]3.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 54[/TD]
[TD]2[/TD]
[TD]3.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 55[/TD]
[TD]-2[/TD]
[TD]2.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 56[/TD]
[TD]-2[/TD]
[TD]2.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 57[/TD]
[TD]1[/TD]
[TD]2.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 58[/TD]
[TD]-3[/TD]
[TD]2.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 59[/TD]
[TD]6[/TD]
[TD]2.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 60[/TD]
[TD]3[/TD]
[TD]2.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 61[/TD]
[TD]-3[/TD]
[TD]3.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 62[/TD]
[TD]2[/TD]
[TD]2.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 63[/TD]
[TD]-2[/TD]
[TD]2.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 64[/TD]
[TD]-4[/TD]
[TD]2.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 65[/TD]
[TD]-2[/TD]
[TD]2.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 66[/TD]
[TD]8[/TD]
[TD]3.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 67[/TD]
[TD]7[/TD]
[TD]3.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 68[/TD]
[TD]-2[/TD]
[TD]3.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 69[/TD]
[TD]2[/TD]
[TD]3.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 70[/TD]
[TD]-3[/TD]
[TD]3.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 71[/TD]
[TD]2[/TD]
[TD]3.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 72[/TD]
[TD]5[/TD]
[TD]3.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 73[/TD]
[TD]2[/TD]
[TD]3.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 74[/TD]
[TD]-2[/TD]
[TD]3.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 75[/TD]
[TD]-2[/TD]
[TD]3.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 9"]However when I Copy this same formula to the next set of Cells it comes out like this[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cell Q[/TD]
[TD]Cell U[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 90[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 91[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 92[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 93[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]0.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 94[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]0.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 95[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 96[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]0.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 97[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 98[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 99[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]0.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 100[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 101[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 102[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 103[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 104[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]0.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 105[/TD]
[TD="align: right"]-4[/TD]
[TD="align: right"]0.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 106[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]0.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 107[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 108[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 109[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 110[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 111[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 112[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]0.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 113[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 114[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 115[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 116[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 117[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 118[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 119[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 120[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 121[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 122[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 123[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 124[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 125[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 126[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]0.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 127[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]0.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 128[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 129[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 130[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 131[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 132[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 133[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 134[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 135[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 136[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 137[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 138[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 139[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 140[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 141[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 142[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]0.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 143[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]0.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 144[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 145[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 146[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 147[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 148[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 149[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 150[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 151[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 152[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 153[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 154[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 155[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 156[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 157[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 158[/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 159[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 160[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 161[/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]1.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 162[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]1.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 163[/TD]
[TD="align: right"]-11[/TD]
[TD="align: right"]1.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 164[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]0.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 165[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]0.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]What am I doing wrong when I copy the cell to the next set of rows in Row 90?[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks again
Zotah
 
Upvote 0
i think u r not putting in the first formula correctly..
u have to put the first formula, then the second formula below.. n drag it...
did u put the first formula in U90 in this case? it should hav been
----> =IF(Q90<0,Q90*-1,Q90)

then below it u put
--->
=IF(Q91<0,(Q91*-1)+(U90*COUNT($Q$90:Q90)),Q91+(U90*COUNT($Q$90:Q90)))/COUNT($Q$90:Q91)
and then drag it
 
Upvote 0
However, if the row 90 has to b considered as a continuation of the set of information that is already previously present .... range Q2:Q75 in your case... you need to delete the spaces betwwen the 2 set of data..
means u have to keep it in continuation so that after row 75, the data continues from row 76...

but if this is a new set of data, and Q90 and Q91 are considered as first 2 numbers just like the case in Q2 and Q3 in the earlier set, then u can use the formula i gave u,,, its same formula, just mapped as per ur requirement
 
Upvote 0
However, if the row 90 has to b considered as a continuation of the set of information that is already previously present .... range Q2:Q75 in your case... you need to delete the spaces betwwen the 2 set of data..
means u have to keep it in continuation so that after row 75, the data continues from row 76...

but if this is a new set of data, and Q90 and Q91 are considered as first 2 numbers just like the case in Q2 and Q3 in the earlier set, then u can use the formula i gave u,,, its same formula, just mapped as per ur requirement


It looks to me like the first set of formulas you gave me are working perfectly however I found when I moved or copied the formula from one set of cell to another it trapped the Q2 and Q3 like you said.

While I was waiting for your responce I was only able to correct it by manually changing all the set cells in the formula to the correct cell figure. I had to change 3 cell figures on each copy and paste set of figures.

=IF(Q91<0,(Q91*-1)+(U90*COUNT($Q$90:Q90)),Q91+(U90*COUNT($Q$90:Q90)))/COUNT($Q$90:Q91)

the underlines of the 90's are what I have to change each time I copy and paste it to make the formula work.

By going into the formula I can create the answer I want after typing in the correct cell each and every time I've pasted it into a new set of cells.

How can I make these formula duplicate the answer for the right cells without having to manually fix each one at a time.

Thanks

Zotah
 
Upvote 0
for that you need a macro..
u cant rely on pre-fed excel forumla for everything...

how many times do u have to paste this formula on different sets?
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top