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