Formula to calculate % different

[seercoven]

Hi All,

I got a file attached which I need to find the different between the 2 period.

Details of my files
1. Period refer to the forecast I need throughout the month of Dec 16, Jan 17, Mar 17....etc.
2. Type refer to individual item I need to find.

Normally to find the different between in month using Apple as example, I would use the forecast between Nov & Dec period.
For the month of Dec, I would use (C5/C6-1) to find out the status between the 2 period.

My data is long so if I want to find out the status for one of the type, is there a generic formula for it if I do a filter. I'm thinking of using subtotal which only calculate those visual table but I can't use division on it. Any suggestion to help me out because I don't want to list out the formula for all of them. My attached file shows the yellow portion that I need the result in that format with apple as an example.

[wanmuhd]

Hi

Would you please show the mock-up of the result that you intended to have

[wanmuhd]

Hi

Would you please show the mock-up of the result that you intended to have

[seercoven]

hi wanmuhd,
Thanks for taking the time to look through and helping out. In the attached file, I've already have the result I want in yellow using apple as example.

[JohnTopley]

Your table is "confusing" (to me!): for Dec16 , the calculation is C5/C6-1 but for Jan 17 onward it is C5/D5-1 etc.

Why aren't the periods all headings?

Or are we to assume the period in row 6 is ALWAYS the period prior to row 5 i.e. D6 is DEC for Apple?

[seercoven]

Hi John,

Or are we to assume the period in row 6 is ALWAYS the period prior to row 5 i.e. D6 is DEC for Apple?

You are right to assume the period in row 6 is prior to row 5. Row 8 is prior to Row 7.

For Dec, I would normally use C5/C6-1. As for Jan 17, I would normally use D5/D6-1. Feb 17, E5/E6-1.
For banana would be C7/C8-1 (Dec), D7/D8-1 (Jan 17), E7/E8-1 (Feb 17) etc...

If I were to do it for every type, the amount of formula needed will slow down the loading of my file. Is there a generic formula I can use so that I can see 1 type at one time? Perhaps using subtotal where I filter my type and I can see my different between 2 period of forecast. (doesn't seems to work with division from my test)

[JohnTopley]

I am now "out of the office" for most of the day. Look at INDEX function to select your data, MATCHing with selected TYPE.

Search on INDEX/MATCH on the Internet.

[seercoven]

Hi John,
I tried using INDEX(C:C,MATCH("Dec",B:B,0))/INDEX(C:C,MATCH("Nov",B:B,0))-1 to get Dec result. It only work for the first type.

Do I add in more condition using IF/ Ifelse?

[tamthat]

Put in A1: Apple

Please Login or Register  to view this content.

[bebo021999]

Try with LOOKUP:

Please Login or Register  to view this content.

With A1 contain Apple, Banana,...

[seercoven]

Hi tamthat & bebo,
Thanks for the help. The formula works great but both require me to input the type in A1.

Is it possible to apply it in such a way like using subtotal??

I just filter apple and i'll get result want and if I change another type I get another result. If I filter with >1 type it shows error or something?

[JohnTopley]

I think it is quicker to enter the type (or use a dropdown list) then use sub-total or filter.