Allocate Yearly Cost Across a Different # of Months for Each Record

**kclynn** · 03-31-2020, 09:03 AM

Hello,
I have figured out how to allocate the cost across the different months but I want the very next cell in the array to have a different calculation based sum of the array. I am sure that I am not explaining this well. Example: I want the number in a cell (say 200,000) to be allocated over duration indicated in a cell (duration 7) then the very next cell (8th month) to but the sum of the 7 months array subtracted from the cell holding the 200,000.

Here are my formulas:
=IF(COLUMNS($R68:R68)>$P68,"",($I68*0.64/$P68)) This is in the first 7 cells
=I68-(SUM(R68:V68)) this is in the 8 cell

Is there a way to combine this so it would be automatically calculated no matter what the duration number is.

**Pepe Le Mokko** · 03-31-2020, 09:29 AM

Administrative note

Welcome to the forum

in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

Thanks you for helping us help you

**Pepe Le Mokko** · 03-31-2020, 09:29 AM

Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
Thanks

**TMS** · 03-31-2020, 04:23 PM

Try:

Cell R68, copied across to cell AD68:

Formula:

Please Login or Register  to view this content.

This will cater for distribution across (up to) 12 months with the remainder in the next column.

**TMS** · 03-31-2020, 04:43 PM

A better version which avoids circular references if column P is blank or zero.

Formula:

Please Login or Register  to view this content.

Allocate Yearly Cost Across a Different # of Months for Each Record

LinkBack

Thread Tools

Rate This Thread

Display

Allocate Yearly Cost Across a Different # of Months for Each Record

Re: Allocate Yearly Cost Across a Different # of Months for Each Record

Re: Allocate Yearly Cost Across a Different # of Months for Each Record

Re: Allocate Yearly Cost Across a Different # of Months for Each Record

Re: Allocate Yearly Cost Across a Different # of Months for Each Record

Thread Information

Users Browsing this Thread

Similar Threads

allocate items cost unless ends in...

Formula to allocate revenues yearly

[SOLVED] Help to allocate cost with vlookup or index match ....

[SOLVED] Calculate cost per month inlcuding cost of former months

[SOLVED] Comparing Record and associated cost with another record and associated cost

[SOLVED] How to distribute the cost to yearly wise

Cost Driver Analysis - Need formula to allocate manpower cost

Bookmarks

Bookmarks

Posting Permissions