• 464K Members
• 12.2K Online
• 560K Conversations

Deleted
Not applicable

# Formula that will update values as actuals are entered

Hi,

I have a formula query for everone out there.

I have a budget which is pulled from a separate tab laying out the dates and values (see columns A-F)

This is then spread evenly across the period of the expenditure of the project.

This is a budget however, Then as invoices come in, I am looking to hardcode in the actual invoice value. (e.g what is in red in cells H15:O18.

What I am looking for is a way to update the formula, so that when I enter an actual, the remaining budget e.g. cells P15:V18 spread the remaining budget across the remaining period.

e.g. P15 should calculate to be 50,000 and P17:T17 should be 40,000 each.

The current formula is:

=AND(\$B\$5<=H13,\$C\$5>=H13)*\$F\$5/\$D\$5

B5 = cost start date

C5 = cost finish date

H13 = actual date

F5 = total budget

D5 = number of periods cost is spread over

Any help will be appreciated

2 Replies

# Re: Formula that will update values as actuals are entered

Hi you can record a macro on that. Then bind it to a button In your workbook. If it’s in an excel online file, make a Flow to get this stuff done, Eva

# Re: Formula that will update values as actuals are entered

Hi,

You may try

`=(\$F5-SUMIFS(\$H15:O15,\$H\$13:O\$13,">="&\$B5,\$H\$13:O\$13,"<="&\$C5))/(YEARFRAC(P\$13,\$C5)*12+1)*(\$B5<=P\$13)*(\$C5>=P\$13)`

Please see in attached file.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies