SOLVED
Home

Subtract numbers until gone

%3CLINGO-SUB%20id%3D%22lingo-sub-358006%22%20slang%3D%22en-US%22%3ESubtract%20numbers%20until%20gone%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358006%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20with%203%20points%20of%20data.%3C%2FP%3E%3COL%3E%3CLI%3ENumber%20to%20use%20per%20week%3C%2FLI%3E%3CLI%3ETotal%20number%3C%2FLI%3E%3CLI%3EWeek%20breakout%3C%2FLI%3E%3C%2FOL%3E%3CP%3EI%20need%20to%20take%20the%20number%20per%20week%20starting%20at%20week%20one%20then%20for%20week%202%2C%203%2C%20etc%20use%20the%20same%20number%20until%20the%20total%20number%20has%20been%20used.%26nbsp%3B%20I%20have%20attached%20an%20example%20to%20show%20what%20I%20am%20talking%20about.%26nbsp%3B%20Thanks%20in%20advanced%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-358006%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358053%22%20slang%3D%22en-US%22%3ERe%3A%20Subtract%20numbers%20until%20gone%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358053%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20step%20is%20to%20link%20the%20%3CSTRONG%3EWeek1%3C%2FSTRONG%3E%20cell%20to%20the%20%3CSTRONG%3ENumber%20to%20subtract%20per%20week%20%3C%2FSTRONG%3Eusing%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DA2%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20second%20step%2C%20use%20this%20formula%20in%3CSTRONG%3E%20Week2%20%3C%2FSTRONG%3Eand%20drag%20it%20to%20the%20right%3A%3C%2FP%3E%3CPRE%3E%3CSTRONG%3EIF(SUM(%24D2%3AD2%2C%24A2)%26lt%3B%3D%24B2%2C%24A2%2CIF(%24B2-SUM(%24D2%3AD2)%3D0%2C%22%22%2C%24B2-SUM(%24D2%3AD2)))%3C%2FSTRONG%3E%3C%2FPRE%3E%3CP%3E%3CSTRONG%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F84645i8761746CACCE9C3A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Subtract%20numbers%20until%20gone.png%22%20title%3D%22Subtract%20numbers%20until%20gone.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EHaytham%3C%2FP%3E%3C%2FLINGO-BODY%3E
dfidler
Occasional Visitor

I have a spreadsheet with 3 points of data.

  1. Number to use per week
  2. Total number
  3. Week breakout

I need to take the number per week starting at week one then for week 2, 3, etc use the same number until the total number has been used.  I have attached an example to show what I am talking about.  Thanks in advanced

1 Reply
Solution

Hi,

 

The first step is to link the Week1 cell to the Number to subtract per week using this formula:

=A2

 

The second step, use this formula in Week2 and drag it to the right:

IF(SUM($D2:D2,$A2)<=$B2,$A2,IF($B2-SUM($D2:D2)=0,"",$B2-SUM($D2:D2)))

Subtract numbers until gone.png

 

 

Regards,

Haytham

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies