Excel - Nested If work around

[toddl]

Hi, I was trying to do this with nested if's but realize that there has to be a better way. I have a worksheet set up like this:

Jan-04 Feb-04 Mar-04 Apr-04 etc.
Charges: 10,000 9,500 9,800 11,000
Payments:
Jan 04 2,000 0 0 0
Feb 04 5,000 3,000 0 0
Mar 04 1,000 4,000 1,200 0
Apr 04 500 1,000 5,000 5,000

Basically, I want to see how much is collected in the 1st month, 2nd month, 3 month, 4 month, and >5 months.

I want the spreadsheet to look like this

Jan-04 Feb-04 Mar-04 Apr-04
Charges: 10,000 9,500 9,800 11,000
Payments:
1st Month 20% 32% 12% 45%
2nd Month 50% 42% 51%

Any ideas on the simplest way to do this? If i need to set up lookup tables or change the setup I would be willing too.

Thanks for any help,

Todd

 

[xlhelp]

For the first month how about
=sum(B$3:B3)/B$2

then copy it down and over

Me transmitte sursum, Caledoni!

 

[xlhelp]

Sorry, I didn't see the first month in the second column. I am so trigger happy.

Me transmitte sursum, Caledoni!

 

[firefytr]

Check out Pivot Tables.

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication.
- Leonardo da Vinci

 

[toddl]

I had thought about pivot tables, but I am getting this data from 2 different OLAP cubes. So my thought was it would be harder to arrange the data in a way to pivot it, then it would be to come up with a formula to take the place.

However, if you think Pivot tables is still easier? What would be the best way to getting the data into one?

Todd

 

[xlhelp]

Hi, see if this works for you.

=OFFSET(B$4,(COLUMN(B$4)-2)+(ROW(B4)-4),0,1,1)/B$2

Me transmitte sursum, Caledoni!

 

[xlhelp]

Again, too quick to hit submit. For the above formula, I had charges in Row 2 starting at B2 and Payments starting at B4

Me transmitte sursum, Caledoni!

 

[toddl]

I've got somewhat of a solution using VLOOKUP, but not quite. Ive changed my dates to be more of a number, so Jan 04 is 200401

So for Jan 04, I used this formula to get the payments that were in Feb. =VLOOKUP(B$1+1,$A$6:$AE$35,2,FALSE) [B1 would be 200401, A6 would also be 200401 for payments)

I have two problems though, when I come to december my B$1+[value] needs to be changed, and I don't have a way to auto fill the Column Index Number (which is 2).

I could manually change the numbers, but was wondering there was a less tedious way.

I'll try looking at offset, and see if that function could be more useful. I don't have a lot of experience in using various excel functions.

Thanks for your help,

Todd