1 Reply Latest reply on Dec 17, 2016 10:34 PM by Shinichiro Murakami

# Calculate profit for years in which certain data is available

hi all,

I have hierachical sales (10 years history) and costs data (2/3/4 years history) at the following levels. Im trying to create a calculated field "PROFIT" for only the years that i have sales and costs data bearing in mind some products have 2 or 3 or 4 years historical costs data. How would i write a formula to

1) Create profitability by year which reflects the amount of data is available. Ie if there is 2 years costs data,id like to zee 2 years profitability. If there is 4 years costs, Id like to see 4 years profitability and see the other years blanked out

2) Be able to measure profitability at the each of the hierachical levels below

SALES: 10 years of Sales data with following levels

1) Brand

2) Franchise (which contains multiple brands)'

3) Business Unit which contains multiple franchises)

4) Country (Which contains multiple Business Units)

COSTS: 2 years of Costs data which is only displayed at

1) Brand level

• ###### 1. Re: Calculate profit for years in which certain data is available

Ian,

It's quite difficult to investigate this without seeing your data.

Please attach packaged workbook.

Thanks,

Shin