Easier way to calcuate average change?

I have a product that is getting sent out to the field as it is manufactured and used daily, the use rate changes from day to day based on how the job is going.

I am trying to calculate the average amount of this product used on a daily basis. I need to subtract the amount used from one day to the next, and average them out.

Is there an easier way to do this than =average((a2-a1),(a3-a2),etc) ?

edited Dec 19 '18 at 23:55

Blackwood

2,88861728

asked Dec 19 '18 at 23:39

user7551463

1

See here: superuser.com/questions/1381471/…

– Scott Craner
Dec 19 '18 at 23:50

What type of average (mode, mean, median, etc.)?

– Ron Maupin
Dec 20 '18 at 9:15

@RonMaupin the word average used loosely as in the above question usually refers to the mean average, and there are also functions specifically for MODE and MEDIAN, where as AVERAGE used in excel also uses MEAN average

– PeterH
Dec 21 '18 at 10:28

add a comment |

I have a product that is getting sent out to the field as it is manufactured and used daily, the use rate changes from day to day based on how the job is going.

I am trying to calculate the average amount of this product used on a daily basis. I need to subtract the amount used from one day to the next, and average them out.

Is there an easier way to do this than =average((a2-a1),(a3-a2),etc) ?

edited Dec 19 '18 at 23:55

Blackwood

2,88861728

asked Dec 19 '18 at 23:39

user7551463

1

See here: superuser.com/questions/1381471/…

– Scott Craner
Dec 19 '18 at 23:50

What type of average (mode, mean, median, etc.)?

– Ron Maupin
Dec 20 '18 at 9:15

@RonMaupin the word average used loosely as in the above question usually refers to the mean average, and there are also functions specifically for MODE and MEDIAN, where as AVERAGE used in excel also uses MEAN average

– PeterH
Dec 21 '18 at 10:28

add a comment |

I have a product that is getting sent out to the field as it is manufactured and used daily, the use rate changes from day to day based on how the job is going.

I am trying to calculate the average amount of this product used on a daily basis. I need to subtract the amount used from one day to the next, and average them out.

Is there an easier way to do this than =average((a2-a1),(a3-a2),etc) ?

edited Dec 19 '18 at 23:55

Blackwood

2,88861728

asked Dec 19 '18 at 23:39

user7551463

I have a product that is getting sent out to the field as it is manufactured and used daily, the use rate changes from day to day based on how the job is going.

I am trying to calculate the average amount of this product used on a daily basis. I need to subtract the amount used from one day to the next, and average them out.

Is there an easier way to do this than =average((a2-a1),(a3-a2),etc) ?

microsoft-excel worksheet-function average

edited Dec 19 '18 at 23:55

Blackwood

2,88861728

asked Dec 19 '18 at 23:39

user7551463

edited Dec 19 '18 at 23:55

Blackwood

2,88861728

asked Dec 19 '18 at 23:39

user7551463

edited Dec 19 '18 at 23:55

Blackwood

2,88861728

edited Dec 19 '18 at 23:55

Blackwood

2,88861728

edited Dec 19 '18 at 23:55

Blackwood

2,88861728

asked Dec 19 '18 at 23:39

user7551463

asked Dec 19 '18 at 23:39

user7551463

asked Dec 19 '18 at 23:39

user7551463

1

See here: superuser.com/questions/1381471/…

– Scott Craner
Dec 19 '18 at 23:50

What type of average (mode, mean, median, etc.)?

– Ron Maupin
Dec 20 '18 at 9:15

@RonMaupin the word average used loosely as in the above question usually refers to the mean average, and there are also functions specifically for MODE and MEDIAN, where as AVERAGE used in excel also uses MEAN average

– PeterH
Dec 21 '18 at 10:28

add a comment |

1

See here: superuser.com/questions/1381471/…

– Scott Craner
Dec 19 '18 at 23:50

What type of average (mode, mean, median, etc.)?

– Ron Maupin
Dec 20 '18 at 9:15

@RonMaupin the word average used loosely as in the above question usually refers to the mean average, and there are also functions specifically for MODE and MEDIAN, where as AVERAGE used in excel also uses MEAN average

– PeterH
Dec 21 '18 at 10:28

See here: superuser.com/questions/1381471/…

– Scott Craner
Dec 19 '18 at 23:50

What type of average (mode, mean, median, etc.)?

– Ron Maupin
Dec 20 '18 at 9:15

@RonMaupin the word average used loosely as in the above question usually refers to the mean average, and there are also functions specifically for MODE and MEDIAN, where as AVERAGE used in excel also uses MEAN average

– PeterH
Dec 21 '18 at 10:28

add a comment |

3 Answers
3

active

oldest

votes

If column A includes the cumulative usage at the end of every day, you don't need to use the AVERAGE function at all. You can calculate the average by subtracting the first value from the last value and dividing by the number of days.

Enter this formula in B2 (or any empty cell in row 2)

=(A2-A$1)/ROWS(A$2:A2)

And copy it down to the other cells in the same column. B2 will now show the average daily usage after one day, C2 will show the average daily usage over the first two days, and so on.

Example of usage

answered Dec 20 '18 at 17:35

Blackwood

2,88861728

Of course! Now I feel stupid! I knew there was a better way!

– user7551463
Dec 20 '18 at 17:43

add a comment |

I would just add a column "Delta" subtracting a1-a2 on b2, a2-a3 on b3 and so on - easy to drag & drop down ... and calculate the average of that column

answered Dec 20 '18 at 9:13

GeraldDC

I thought about that, but that data isn't something that we are using, and I'm currently in the process of trying to make this spreadsheet less bulky.

– user7551463
Dec 20 '18 at 17:43

add a comment |

-1

Considering popular business practices, I would like to suggest Weighted Average method to find the forthcoming price of products in Table.

To achieve the goal you need few other information to add to the Sales Table are, Quantity Sold and Total Sale.

Below is the Sample Table along with calculated values, finds the Average Price of each product.

The mechanism behind the calculation is:

(First Day Price*First Day Qty)+(Second Day Price*Second Day Qty)+,,,/Total Qty

enter image description here

How it works:

Data Range for the Sales Table is A1:E13 including Headers.

Formula for Quantity Sold in Cell B16 is:
```
=SUMIFS($D$2:$D$13,$A$2:$A$135,  $A16)
```

Cell C16 has Formula for Total Sale.
```
=SUMIFS($E$2:$E$13,$A$2:$A$13,$A16)
```

Average Price Formula in Cell D16.
```
=CEILING(C16/D16,2)
```

N.B.

Fill all the Formula Down.

I've used the CEILING Function to Round up the Price, you may use ROUND or ROUNDUP also.

Adjust cell references in the Formula as needed.

answered Dec 20 '18 at 10:14

Rajesh S

Write your concern,, why down voted this post??

– Rajesh S
Dec 23 '18 at 6:01

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1386089%2feasier-way-to-calcuate-average-change%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

Enter this formula in B2 (or any empty cell in row 2)

=(A2-A$1)/ROWS(A$2:A2)

And copy it down to the other cells in the same column. B2 will now show the average daily usage after one day, C2 will show the average daily usage over the first two days, and so on.

Example of usage

answered Dec 20 '18 at 17:35

Blackwood

2,88861728

Of course! Now I feel stupid! I knew there was a better way!

– user7551463
Dec 20 '18 at 17:43

add a comment |

Enter this formula in B2 (or any empty cell in row 2)

=(A2-A$1)/ROWS(A$2:A2)

And copy it down to the other cells in the same column. B2 will now show the average daily usage after one day, C2 will show the average daily usage over the first two days, and so on.

Example of usage

answered Dec 20 '18 at 17:35

Blackwood

2,88861728

Of course! Now I feel stupid! I knew there was a better way!

– user7551463
Dec 20 '18 at 17:43

add a comment |

Enter this formula in B2 (or any empty cell in row 2)

=(A2-A$1)/ROWS(A$2:A2)

And copy it down to the other cells in the same column. B2 will now show the average daily usage after one day, C2 will show the average daily usage over the first two days, and so on.

Example of usage

answered Dec 20 '18 at 17:35

Blackwood

2,88861728

Enter this formula in B2 (or any empty cell in row 2)

=(A2-A$1)/ROWS(A$2:A2)

And copy it down to the other cells in the same column. B2 will now show the average daily usage after one day, C2 will show the average daily usage over the first two days, and so on.

Example of usage

answered Dec 20 '18 at 17:35

Blackwood

2,88861728

answered Dec 20 '18 at 17:35

Blackwood

2,88861728

answered Dec 20 '18 at 17:35

Blackwood

2,88861728

answered Dec 20 '18 at 17:35

Blackwood

2,88861728

Of course! Now I feel stupid! I knew there was a better way!

– user7551463
Dec 20 '18 at 17:43

add a comment |

Of course! Now I feel stupid! I knew there was a better way!

– user7551463
Dec 20 '18 at 17:43

Of course! Now I feel stupid! I knew there was a better way!

– user7551463
Dec 20 '18 at 17:43

add a comment |

I would just add a column "Delta" subtracting a1-a2 on b2, a2-a3 on b3 and so on - easy to drag & drop down ... and calculate the average of that column

answered Dec 20 '18 at 9:13

GeraldDC

I thought about that, but that data isn't something that we are using, and I'm currently in the process of trying to make this spreadsheet less bulky.

– user7551463
Dec 20 '18 at 17:43

add a comment |

I would just add a column "Delta" subtracting a1-a2 on b2, a2-a3 on b3 and so on - easy to drag & drop down ... and calculate the average of that column

answered Dec 20 '18 at 9:13

GeraldDC

I thought about that, but that data isn't something that we are using, and I'm currently in the process of trying to make this spreadsheet less bulky.

– user7551463
Dec 20 '18 at 17:43

add a comment |

I would just add a column "Delta" subtracting a1-a2 on b2, a2-a3 on b3 and so on - easy to drag & drop down ... and calculate the average of that column

answered Dec 20 '18 at 9:13

GeraldDC

I would just add a column "Delta" subtracting a1-a2 on b2, a2-a3 on b3 and so on - easy to drag & drop down ... and calculate the average of that column

answered Dec 20 '18 at 9:13

GeraldDC

answered Dec 20 '18 at 9:13

GeraldDC

answered Dec 20 '18 at 9:13

GeraldDC

answered Dec 20 '18 at 9:13

GeraldDC

I thought about that, but that data isn't something that we are using, and I'm currently in the process of trying to make this spreadsheet less bulky.

– user7551463
Dec 20 '18 at 17:43

add a comment |

I thought about that, but that data isn't something that we are using, and I'm currently in the process of trying to make this spreadsheet less bulky.

– user7551463
Dec 20 '18 at 17:43

I thought about that, but that data isn't something that we are using, and I'm currently in the process of trying to make this spreadsheet less bulky.

– user7551463
Dec 20 '18 at 17:43

add a comment |

-1

Considering popular business practices, I would like to suggest Weighted Average method to find the forthcoming price of products in Table.

To achieve the goal you need few other information to add to the Sales Table are, Quantity Sold and Total Sale.

Below is the Sample Table along with calculated values, finds the Average Price of each product.

The mechanism behind the calculation is:

(First Day Price*First Day Qty)+(Second Day Price*Second Day Qty)+,,,/Total Qty

enter image description here

How it works:

Data Range for the Sales Table is A1:E13 including Headers.

Formula for Quantity Sold in Cell B16 is:
```
=SUMIFS($D$2:$D$13,$A$2:$A$135,  $A16)
```

Cell C16 has Formula for Total Sale.
```
=SUMIFS($E$2:$E$13,$A$2:$A$13,$A16)
```

Average Price Formula in Cell D16.
```
=CEILING(C16/D16,2)
```

N.B.

Fill all the Formula Down.

I've used the CEILING Function to Round up the Price, you may use ROUND or ROUNDUP also.

Adjust cell references in the Formula as needed.

answered Dec 20 '18 at 10:14

Rajesh S

Write your concern,, why down voted this post??

– Rajesh S
Dec 23 '18 at 6:01

add a comment |

-1

Considering popular business practices, I would like to suggest Weighted Average method to find the forthcoming price of products in Table.

To achieve the goal you need few other information to add to the Sales Table are, Quantity Sold and Total Sale.

Below is the Sample Table along with calculated values, finds the Average Price of each product.

The mechanism behind the calculation is:

(First Day Price*First Day Qty)+(Second Day Price*Second Day Qty)+,,,/Total Qty

enter image description here

How it works:

Data Range for the Sales Table is A1:E13 including Headers.

Formula for Quantity Sold in Cell B16 is:
```
=SUMIFS($D$2:$D$13,$A$2:$A$135,  $A16)
```

Cell C16 has Formula for Total Sale.
```
=SUMIFS($E$2:$E$13,$A$2:$A$13,$A16)
```

Average Price Formula in Cell D16.
```
=CEILING(C16/D16,2)
```

N.B.

Fill all the Formula Down.

I've used the CEILING Function to Round up the Price, you may use ROUND or ROUNDUP also.

Adjust cell references in the Formula as needed.

answered Dec 20 '18 at 10:14

Rajesh S

Write your concern,, why down voted this post??

– Rajesh S
Dec 23 '18 at 6:01

add a comment |

-1

Considering popular business practices, I would like to suggest Weighted Average method to find the forthcoming price of products in Table.

To achieve the goal you need few other information to add to the Sales Table are, Quantity Sold and Total Sale.

Below is the Sample Table along with calculated values, finds the Average Price of each product.

The mechanism behind the calculation is:

(First Day Price*First Day Qty)+(Second Day Price*Second Day Qty)+,,,/Total Qty

enter image description here

How it works:

Data Range for the Sales Table is A1:E13 including Headers.

Formula for Quantity Sold in Cell B16 is:
```
=SUMIFS($D$2:$D$13,$A$2:$A$135,  $A16)
```

Cell C16 has Formula for Total Sale.
```
=SUMIFS($E$2:$E$13,$A$2:$A$13,$A16)
```

Average Price Formula in Cell D16.
```
=CEILING(C16/D16,2)
```

N.B.

Fill all the Formula Down.

I've used the CEILING Function to Round up the Price, you may use ROUND or ROUNDUP also.

Adjust cell references in the Formula as needed.

answered Dec 20 '18 at 10:14

Rajesh S

Considering popular business practices, I would like to suggest Weighted Average method to find the forthcoming price of products in Table.

To achieve the goal you need few other information to add to the Sales Table are, Quantity Sold and Total Sale.

Below is the Sample Table along with calculated values, finds the Average Price of each product.

The mechanism behind the calculation is:

(First Day Price*First Day Qty)+(Second Day Price*Second Day Qty)+,,,/Total Qty

enter image description here

How it works:

Data Range for the Sales Table is A1:E13 including Headers.

Formula for Quantity Sold in Cell B16 is:
```
=SUMIFS($D$2:$D$13,$A$2:$A$135,  $A16)
```

Cell C16 has Formula for Total Sale.
```
=SUMIFS($E$2:$E$13,$A$2:$A$13,$A16)
```

Average Price Formula in Cell D16.
```
=CEILING(C16/D16,2)
```

N.B.

Fill all the Formula Down.

I've used the CEILING Function to Round up the Price, you may use ROUND or ROUNDUP also.

Adjust cell references in the Formula as needed.

answered Dec 20 '18 at 10:14

Rajesh S

answered Dec 20 '18 at 10:14

Rajesh S

answered Dec 20 '18 at 10:14

Rajesh S

answered Dec 20 '18 at 10:14

Rajesh S

Write your concern,, why down voted this post??

– Rajesh S
Dec 23 '18 at 6:01

add a comment |

Write your concern,, why down voted this post??

– Rajesh S
Dec 23 '18 at 6:01

Write your concern,, why down voted this post??

– Rajesh S
Dec 23 '18 at 6:01

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Super User!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Gfrktyl