Formula Forensic No. 015 – Cornelia’s Price Rises

Formula Forensic No. 015 – Cornelia’s Price Rises:
In March Cornelia asked a question at Chandoo.org:
“I Want to create a formula for my price list, every time the prices goes up a certain %, I just want to edit the % sell, then my entire spread sheet needs to change accordingly.”
I responded with a simple array formula
=A2*PRODUCT(1+PriceRises/100)

So today we will pull Hui’s Solution apart to see what’s inside.

Hui’s Solution

As usual we will work through this formula using a sample file for you to follow along. Download Here.
Hui’s formula is an array formula
{=A2*PRODUCT(1+PriceRises/100)}
This formula is a simple Original Price * Factor style formula
Where the Factor is PRODUCT(1+PriceRises/100)
Which is calculating the cumulative price rise based on a list of individual price rises in a Named Formula called “PriceRises”

Going into the Name Manager we can see that the Named Formula “Price Rises” is made up of the formula
=OFFSET(Sheet1!$E$2,,,COUNTA(Sheet1!$E:$E)-1,1)
This is a simple offset formula which will return the list of Price Rises from Column E
Offset uses the Reference E2 and the counts the number of entries in Column E and subtracts 1 (for the header row)
In a Blank cell say L8 enter =OFFSET(Sheet1!$E$2,,,COUNTA(Sheet1!$E:$E)-1,1) Then press F9
Excel will return ={4,5,6}
Which is a list of the 3 Price Rises in the Price Rise column.
So back to the equation PRODUCT(1+PriceRises/100)

We can see that it consists of a Product() function which will multiply the values of the constituent numbers together.
PRODUCT(1+PriceRises/100)
But the constituent numbers consists of a Formula 1+PriceRises/100
In a spare cell say L10 enter the formula =1+PriceRises/100  Then press F9
Excel will return ={1.04, 1.05, 1.06}
We can see here that Excel has taken each entry from the Price Rises Named Formula and divided it by 100 and then added 1 to it
Product then multiplies the numbers together to return the Price Rise factor of 1.15752 = (1.04 * 1.05 * 1.06)
This is finally used to multiply the Original price to arrive at the new sale price

The benefit of using a Named Formula here is that if you add an entry to Column E ie: another price rise, that the PriceRises named formula expands to include that entry and a new Price Rise factor is hence applied to all the entries

Download

You can download a copy of the above file and follow along, Download Here.

Formula Forensics “The Series”

You can learn more about how to pull Excel Formulas apart in the following posts
Formula Forensic Series

We Need Your Help

Formula Forensics is running out of ideas for future Formula Forensics Posts and so I need your help.
If there is an Excel Function that you want explained as in FF07 – Sumproduct let me know
If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post like above or;
If you have a formula that you would like explained but don’t want to write a post also send it to Chandoo or Hui.