Home

Formula that will update values as actuals are entered

%3CLINGO-SUB%20id%3D%22lingo-sub-325969%22%20slang%3D%22en-US%22%3EFormula%20that%20will%20update%20values%20as%20actuals%20are%20entered%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-325969%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20a%20formula%20query%20for%20everone%20out%20there.%3C%2FP%3E%3CP%3EI%20have%20a%20budget%20which%20is%20pulled%20from%20a%20separate%20tab%20laying%20out%20the%20dates%20and%20values%20(see%20columns%20A-F)%3C%2FP%3E%3CP%3EThis%20is%20then%20spread%20evenly%20across%20the%20period%20of%20the%20expenditure%20of%20the%20project.%3C%2FP%3E%3CP%3EThis%20is%20a%20budget%20however%2C%20Then%20as%20invoices%20come%20in%2C%20I%20am%20looking%20to%20hardcode%20in%20the%20actual%20invoice%20value.%20(e.g%20what%20is%20in%20red%20in%20cells%20H15%3AO18.%3C%2FP%3E%3CP%3EWhat%20I%20am%20looking%20for%20is%20a%20way%20to%20update%20the%20formula%2C%20so%20that%20when%20I%20enter%20an%20actual%2C%20the%20remaining%20budget%20e.g.%20cells%20P15%3AV18%20spread%20the%20remaining%20budget%20across%20the%20remaining%20period.%3C%2FP%3E%3CP%3Ee.g.%20P15%20should%20calculate%20to%20be%2050%2C000%20and%20P17%3AT17%20should%20be%2040%2C000%20each.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20current%20formula%20is%3A%3C%2FP%3E%3CP%3E%3DAND(%24B%245%26lt%3B%3DH13%2C%24C%245%26gt%3B%3DH13)*%24F%245%2F%24D%245%3C%2FP%3E%3CP%3EB5%20%3D%20cost%20start%20date%3C%2FP%3E%3CP%3EC5%20%3D%20cost%20finish%20date%3C%2FP%3E%3CP%3EH13%20%3D%20actual%20date%3C%2FP%3E%3CP%3EF5%20%3D%20total%20budget%3C%2FP%3E%3CP%3ED5%20%3D%20number%20of%20periods%20cost%20is%20spread%20over%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20will%20be%20appreciated%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-325969%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-326096%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20that%20will%20update%20values%20as%20actuals%20are%20entered%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-326096%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20try%3C%2FP%3E%0A%3CPRE%3E%3D(%24F5-SUMIFS(%24H15%3AO15%2C%24H%2413%3AO%2413%2C%22%26gt%3B%3D%22%26amp%3B%24B5%2C%24H%2413%3AO%2413%2C%22%26lt%3B%3D%22%26amp%3B%24C5))%2F(YEARFRAC(P%2413%2C%24C5)*12%2B1)*(%24B5%26lt%3B%3DP%2413)*(%24C5%26gt%3B%3DP%2413)%3C%2FPRE%3E%0A%3CP%3EPlease%20see%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-326015%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20that%20will%20update%20values%20as%20actuals%20are%20entered%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-326015%22%20slang%3D%22en-US%22%3EHi%20you%20can%20record%20a%20macro%20on%20that.%20Then%20bind%20it%20to%20a%20button%20In%20your%20workbook.%20If%20it%E2%80%99s%20in%20an%20excel%20online%20file%2C%20make%20a%20Flow%20to%20get%20this%20stuff%20done%2C%20Eva%3C%2FLINGO-BODY%3E
rstern
Occasional Visitor

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
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

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
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies