Hello everyone,
I'm a bit of a novice at this, so hopefully someone will be able to help me.
I am working on a project wherein in must track the percentage of growth across the lifespan of items in 1 year, 3 year, and full lifetime spans.
The sales figures for the item are arranged in order down a single column (I'll say E, in this example). Although the items vary in lifespan and sales data, I'll use an item with a 5 year history for this example. In such a scenario the sales numbers are arranged in E2 - E7 (E2 being the oldest year on record, E7 being most recent). Here is a screenshot:
Uploaded with ImageShack.us
To calculate the percentage change between the two most recent years of sales history, I am using the following formula:
=((E7-E6)/E6)
In order to expand that % across three years, I average the change:
=(((E5-E4)/E4)+((E6-E5)/E5)+((E7-E6)/E6))/3
For the full lifespan, I continue that average to cover the change between each year of the item’s history. For a 5 year lifespan, the formula would look like so:
=(((E3-E2)/E2)+((E4-E3)/E3)+((E5-E4)/4)+((E6-E5)/E5))/4
Where I am running into a problem is when an item has 0 sales for a single period. In going from a year of sales to a year of no sales, it will show an appropriate loss of -100%, however, going from 0 to a year of sales divides by zero and gives me a #DIV/0! Error.
For a single year, I’ve come across an acceptable solutions in using “if” statements. In the below example, assume E6=0:
=if(iserror((E7-E6)/E6),1,((E7-E6)/E6)
This treats the 0 value in such as way at to yield a %100 increase when a number jumps from 0 to any sales number.
Whereas I understand how to apply this to my single year calculations, I do not understand how to write a formula for my 3 year and full lifespan. It has come to my attention that you can “nest” if statements, which seems like it would be a solution; however, I’ve also read that you can only nest up to 7 if statements . . . which presents a problem for items with longer sales histories (they go up to 8 years).
Can anyone help me out?
I'm a bit of a novice at this, so hopefully someone will be able to help me.
I am working on a project wherein in must track the percentage of growth across the lifespan of items in 1 year, 3 year, and full lifetime spans.
The sales figures for the item are arranged in order down a single column (I'll say E, in this example). Although the items vary in lifespan and sales data, I'll use an item with a 5 year history for this example. In such a scenario the sales numbers are arranged in E2 - E7 (E2 being the oldest year on record, E7 being most recent). Here is a screenshot:
Uploaded with ImageShack.us
To calculate the percentage change between the two most recent years of sales history, I am using the following formula:
=((E7-E6)/E6)
In order to expand that % across three years, I average the change:
=(((E5-E4)/E4)+((E6-E5)/E5)+((E7-E6)/E6))/3
For the full lifespan, I continue that average to cover the change between each year of the item’s history. For a 5 year lifespan, the formula would look like so:
=(((E3-E2)/E2)+((E4-E3)/E3)+((E5-E4)/4)+((E6-E5)/E5))/4
Where I am running into a problem is when an item has 0 sales for a single period. In going from a year of sales to a year of no sales, it will show an appropriate loss of -100%, however, going from 0 to a year of sales divides by zero and gives me a #DIV/0! Error.
For a single year, I’ve come across an acceptable solutions in using “if” statements. In the below example, assume E6=0:
=if(iserror((E7-E6)/E6),1,((E7-E6)/E6)
This treats the 0 value in such as way at to yield a %100 increase when a number jumps from 0 to any sales number.
Whereas I understand how to apply this to my single year calculations, I do not understand how to write a formula for my 3 year and full lifespan. It has come to my attention that you can “nest” if statements, which seems like it would be a solution; however, I’ve also read that you can only nest up to 7 if statements . . . which presents a problem for items with longer sales histories (they go up to 8 years).
Can anyone help me out?
