Formula needed to return last instance/2nd to last instance...ect

NicholasP · Jun 25, 2008

Hello,

I'm trying to find rolling percentage differences in my data. For instance, the difference between company A on 3/31 (15) and company A on 12/31 (10) is 50%. And the difference between company A on 3/31 (15) and company A on 9/30 (5) is 200%...

I know I can do this in VB, but was wondering what the formula solution might be.

I would really appreciate any assistance!
Thanks
Nick

Assuming 9/30 is in cell A1

9/30/2007 A 5
12/31/2007 B 1
12/31/2007 C 3
12/31/2007 A 10
3/31/2008 B 2
3/31/2008 C 6
3/31/2008 A 15

Also, sorry about not posting using the HTML maker, but I can't install it at work

NicholasP · Jun 27, 2008

Any thoughts?

Peter_SSs · Jun 28, 2008

Is this what you mean? Note some #DIV/0! errors as 6 month data not available. I didn't try to clean this up because it's already a big enough formula and I'm not sure this is what you want anyway.

Note also my dates are in dd/mm/yyyy format.

Formula in G2 is copied across and down.

Excel Workbook

A

B

C

D

E

F

G

H

1

30/09/2007

A

5

Last date

Company

3 Months

6 Months

2

31/12/2007

B

1

31/03/2008

A

50.00%

200.00%

3

31/12/2007

C

3

B

100.00%

#DIV/0!

4

31/12/2007

A

10

C

100.00%

#DIV/0!

5

31/03/2008

B

2

6

31/03/2008

C

6

7

31/03/2008

A

15

8

Difference

Yogi Anand · Jun 28, 2008

Hi NicolasP:

following is one way ... with a single cell convoluted formula ...

<html><head><title>Excel Jeanie HTML</title></head><body>

Excel Workbook

A

B

C

D

E

1

30-Sep-07

A

5

*

2

31-Dec-07

B

1

A

50%

3

31-Dec-07

C

3

*

4

31-Dec-07

A

10

*

5

31-Mar-08

B

2

*

6

31-Mar-08

C

6

*

7

31-Mar-08

A

15

*

Sheet3 (2)

</body></html>

Yogi Anand · Jun 28, 2008

And following is a solution using Data Table feature in EXCEl ... this uses whole columns

Book1

A

B

C

D

E

F

G

H

I

J

K

1

Date1

Company

Amount

database

2

30-Sep-07

A

5

FALSE

criteria_range

3

31-Dec-07

B

1

DataTable_ColumnInputCell

4

31-Dec-07

C

3

6

9

12

DataTable_RowInputCell

5

31-Dec-07

A

10

A

50%

200%

DataTable_Formula

6

31-Mar-08

B

2

B

100%

7

31-Mar-08

C

6

C

100%

8

31-Mar-08

A

15

9

Sheet3 (3)

NicholasP · Jul 3, 2008

Thanks guys! That was very helpful! I ended up going with Peter's solution due to the way it fit into my spreadsheet. Thanks again!

~Nick

Formula needed to return last instance/2nd to last instance...ect

NicholasP

Active Member

Excel Facts

NicholasP

Active Member

Peter_SSs

MrExcel MVP, Moderator

Yogi Anand

MrExcel MVP

Yogi Anand

MrExcel MVP

NicholasP

Active Member

Similar threads

Forum statistics

Share this page

Formula needed to return last instance/2nd to last instance...ect

NicholasP

Active Member

Excel Facts

NicholasP

Active Member

Peter_SSs

MrExcel MVP, Moderator

Yogi Anand

MrExcel MVP

Yogi Anand

MrExcel MVP

NicholasP

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock