Entering values based on start date

%3CLINGO-SUB%20id%3D%22lingo-sub-2305831%22%20slang%3D%22en-US%22%3EEntering%20values%20based%20on%20start%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2305831%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20find%20a%20way%20to%20automatically%20enter%20predetermined%20values%20(%23%20of%20people)%20into%20cells%20based%20on%20a%20start%20date%20(Quarterly)%20with%20all%20having%20the%20same%20end%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20Company%20A%20would%20have%20100%20employees%20starting%20on%20Q4%202022%20until%20Q4%202023.%20Company%20B%20would%20have%20150%20employees%20starting%20on%20Q1%202023%20until%20Q4%202023.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20bonus%20would%20be%20if%20I%20could%20factor%20in%20a%20ramp-up%20(25%25%20of%20population%20in%20Q1%2C%2050%25%20in%20Q2%2C%20and%20100%25%20in%20Q3).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20would%20save%20me%20from%20manually%20adding%20to%20start%2C%20and%20manually%20making%20any%20changes%20to%20the%20%23%20or%20the%20start%20date.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBG%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2305831%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%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-2306749%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20values%20based%20on%20start%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2306749%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1040453%22%20target%3D%22_blank%22%3E%40BGamble%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20not%20altogether%20clear%20what%20you're%20asking%2C%20and%20how%20the%20sample%20spreadsheet%20represents%20it.%20Among%20other%20things%20that%20are%20ambiguous%2C%20your%20labels%20in%20the%20spreadsheet%20are%20%22Need%22%20and%20%22Want%22%20with%20the%20former%20sounding%20more%20urgent%20(higher%20priority)%20than%20the%20latter%2C%20but%20I%20don't%20really%20think%20that's%20what%20you%20mean.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%2C%20it%20might%20be%20that%20your%20solution%20is%20to%20be%20found%20in%20the%20use%20of%20the%20MIN%20function%2C%20coupled%20with%20a%20separate%20or%20added%20line%20of%20figures%20above%20the%20calendar%20quarters%2C%20the%20added%20line%20showing%20the%20percent%20to%20be%20added%20as%20part%20of%20that%20ramping%20up.%20Here's%20an%20example%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DMIN(%24E16%2CG16%2B(%24E16*H11))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ETake%20a%20look%20at%20the%20attached%2C%20play%20around%20with%20the%20ramping%20up%20percentages%20(by%20the%20way%2C%20I%20added%20ones%20beyond%20the%20point%20of%20100%25%2C%20just%20to%20make%20sure%20the%20numbers%20stopped%20growing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,

 

I would like to find a way to automatically enter predetermined values (# of people) into cells based on a start date (Quarterly) with all having the same end date.

 

For example Company A would have 100 employees starting on Q4 2022 until Q4 2023. Company B would have 150 employees starting on Q1 2023 until Q4 2023.  

 

The bonus would be if I could factor in a ramp-up (25% of population in Q1, 50% in Q2, and 100% in Q3).

 

This would save me from manually adding to start, and manually making any changes to the # or the start date. 

 

Thank you,

 

BG

1 Reply

@BGamble 

 

It's not altogether clear what you're asking, and how the sample spreadsheet represents it. Among other things that are ambiguous, your labels in the spreadsheet are "Need" and "Want" with the former sounding more urgent (higher priority) than the latter, but I don't really think that's what you mean.

 

Anyway, it might be that your solution is to be found in the use of the MIN function, coupled with a separate or added line of figures above the calendar quarters, the added line showing the percent to be added as part of that ramping up. Here's an example

=MIN($E16,G16+($E16*H11))

Take a look at the attached, play around with the ramping up percentages (by the way, I added ones beyond the point of 100%, just to make sure the numbers stopped growing.