SOLVED
Home

Divide a quantity as whole number among multiple cells

%3CLINGO-SUB%20id%3D%22lingo-sub-154139%22%20slang%3D%22en-US%22%3EDivide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-154139%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3EAny%20suggestion%20for%20an%20excel%20formula%20to%20derive%20A2%2C%20B2%2C%20C2%2C%20D2%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Evalues.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEg%3A%3C%2FP%3E%0A%3CTABLE%20style%3D%22border-collapse%3A%20collapse%3B%20width%3A%20288pt%3B%22%20border%3D%220%22%20width%3D%22384%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%0A%3CTBODY%3E%0A%3CTR%20style%3D%22height%3A%2013.0pt%3B%22%3E%0A%3CTD%20width%3D%2264%22%20height%3D%2217%22%20class%3D%22xl64%22%20style%3D%22height%3A%2013.0pt%3B%20width%3A%2048pt%3B%22%3ECategory%3C%2FTD%3E%0A%3CTD%20width%3D%2264%22%20class%3D%22xl64%22%20style%3D%22border-left%3A%20none%3B%20width%3A%2048pt%3B%22%3EIncome%3C%2FTD%3E%0A%3CTD%20width%3D%2264%22%20class%3D%22xl64%22%20style%3D%22border-left%3A%20none%3B%20width%3A%2048pt%3B%22%3EA%3C%2FTD%3E%0A%3CTD%20width%3D%2264%22%20class%3D%22xl64%22%20style%3D%22border-left%3A%20none%3B%20width%3A%2048pt%3B%22%3EB%3C%2FTD%3E%0A%3CTD%20width%3D%2264%22%20class%3D%22xl64%22%20style%3D%22border-left%3A%20none%3B%20width%3A%2048pt%3B%22%3EC%3C%2FTD%3E%0A%3CTD%20width%3D%2264%22%20class%3D%22xl64%22%20style%3D%22border-left%3A%20none%3B%20width%3A%2048pt%3B%22%3ED%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%20style%3D%22height%3A%2012.5pt%3B%22%3E%0A%3CTD%20height%3D%2217%22%20class%3D%22xl63%22%20style%3D%22height%3A%2012.5pt%3B%20border-top%3A%20none%3B%22%3ESal1%3C%2FTD%3E%0A%3CTD%20align%3D%22right%22%20class%3D%22xl63%22%20style%3D%22border-top%3A%20none%3B%20border-left%3A%20none%3B%22%3E11%3C%2FTD%3E%0A%3CTD%20align%3D%22right%22%20class%3D%22xl63%22%20style%3D%22border-top%3A%20none%3B%20border-left%3A%20none%3B%22%3E3%3C%2FTD%3E%0A%3CTD%20align%3D%22right%22%20class%3D%22xl63%22%20style%3D%22border-top%3A%20none%3B%20border-left%3A%20none%3B%22%3E3%3C%2FTD%3E%0A%3CTD%20align%3D%22right%22%20class%3D%22xl63%22%20style%3D%22border-top%3A%20none%3B%20border-left%3A%20none%3B%22%3E3%3C%2FTD%3E%0A%3CTD%20align%3D%22right%22%20class%3D%22xl63%22%20style%3D%22border-top%3A%20none%3B%20border-left%3A%20none%3B%22%3E2%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20we%20divide%2011%20among%204%20columns%2C%202.75%20will%20result%20in%20the%204%20cells%3C%2FP%3E%0A%3CP%3EBut%20I%20need%20the%20excel%20formula%20to%20get%20the%20whole%20number%2C%20next%20integer(3%20for%202.75)%20in%20all%20cells.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAny%20response%20will%20be%20helpful.%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-154139%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-283867%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-283867%22%20slang%3D%22en-US%22%3E%3CP%3EReference%20%3A%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fisformula-function-e4d1355f-7121-4ef2-801e-3839bfd6b1e5%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EIsFormula%20Function%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-283854%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-283854%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%202016%2C%20I%20don't%20know%20what%20isFormula%26nbsp%3Bfunction%20is.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-283828%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-283828%22%20slang%3D%22en-US%22%3EMay%20I%20have%20your%20excel%20version%3F%20Does%20it%20have%20IsFormula%20Function%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-279520%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279520%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20trying%2C%20I%20couldn't%20get%20that%20formula%20to%20work%20at%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-278017%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-278017%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20so%20sorry%20for%20the%20late%20reply.%26nbsp%3B%20I%20am%20not%20sure%20if%20the%20following%20approach%20suits%20your%20needs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Formula%20is%3C%2FP%3E%3CPRE%3E%3D%24A%241%2FSUMPRODUCT(--(ISFORMULA(%24B%241%3A%24F%241)))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3EF%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E50%3C%2FTD%3E%3CTD%3E%3CPRE%3E%3DTheFormula%3C%2FPRE%3E%3C%2FTD%3E%3CTD%3E%3CPRE%3E%3DTheFormula%3C%2FPRE%3E%3C%2FTD%3E%3CTD%3E%3CPRE%3E%3DTheFormula%3C%2FPRE%3E%3C%2FTD%3E%3CTD%3E%3CPRE%3E%3DTheFormula%3C%2FPRE%3E%3C%2FTD%3E%3CTD%3E%3CPRE%3E%3DTheFormula%3C%2FPRE%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-259971%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-259971%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20similar%20thing%20with%20a%20couple%20of%20differences.%20I%20need%20to%20do%20the%20same%20derive%20values%20across%20variable%20columns%20from%20a%20number%20entered%20into%20the%20first%20column%2C%20but%20i%20also%20need%20to%20be%20able%20to%20manually%20override%20the%20formula%20in%20some%20cells%20but%20have%20the%20other%20cells%20adjust%20to%20the%20variable.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%20A1%3D50%2C%20B1%3AF1%20%3D%2010%2C%20but%20if%20I%20manually%20adjust%20C1%20to%20equal%200%2C%20the%20other%204%20cells%20should%20auto-adjust%20to%2012.5%3C%2FP%3E%3CP%3EThe%20formula%20should%20be%20variable%20to%20be%20copied%20across%202%20cells%20or%2020.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThoughts%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAllain%3C%2FP%3E%3CP%3Eallainm%40gmail.com%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-259968%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-259968%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20similar%20thing%20with%20a%20couple%20of%20differences.%20I%20need%20to%20do%20the%20same%20derive%20values%20across%20variable%20columns%20from%20a%20number%20entered%20into%20the%20first%20column%2C%20but%20i%20also%20need%20to%20be%20able%20to%20manually%20override%20the%20formula%20in%20some%20cells%20but%20have%20the%20other%20cells%20adjust%20to%20the%20variable.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%20A1%3D50%2C%20B1%3AF1%20%3D%2010%2C%20but%20if%20I%20manually%20adjust%20C1%20to%20equal%200%2C%20the%20other%204%20cells%20should%20auto-adjust%20to%2012.5%3C%2FP%3E%3CP%3EThe%20formula%20should%20be%20variable%20to%20be%20copied%20across%202%20cells%20or%2020.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThoughts%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAllain%3C%2FP%3E%3CP%3Eallainm%40gmail.com%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-155443%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-155443%22%20slang%3D%22en-US%22%3E%3CP%3Ehmm..Thank%20you%26nbsp%3B%20Willy%20for%20the%20support%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eits%20solved%20my%20issue%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-154554%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-154554%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20is%20because%20your%20sheet%20keeps%20my%20previous%20version%20formula.%26nbsp%3B%20You%20may%20clear%20C2%2C%20D2%2C%20E2%20and%20F2.%26nbsp%3B%20Then%2C%20paste%20the%20latest%20formula%20to%20C2.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-154410%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-154410%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20the%20response%2C%20I%20am%20unable%20to%20get%20the%20expected%20results%3C%2FP%3E%0A%3CP%3EWhen%20I%20am%20pasting%20the%20formula%20in%20C2%2C%20it%20results%20in%20circulate%20reference%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-154365%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-154365%22%20slang%3D%22en-US%22%3E%3CPRE%3E%3DMIN(IF(COLUMN()%3D3%2C%24B2%2C%24B2-SUM(%24C2%3AOFFSET(C2%2C0%2C-1)))%2C%20ROUNDUP(%24B2%2F(SUMPRODUCT(NOT(%241%3A%241%3D0)*1)-2)%2C0))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-154353%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-154353%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Detlef%2C%3C%2FP%3E%0A%3CP%3ESorry%20I%20didn't%20explain%20it%20right!%3C%2FP%3E%0A%3CP%3EI%20look%20for%20auto%20population%20of%20values%20in%20C2%2CD2%2CE2%20and%20F2%20cells.%3C%2FP%3E%0A%3CP%3Eand%20if%20we%20the%20quantity%20to%20divide%20is%205%2C%3C%2FP%3E%0A%3CP%3EI%26nbsp%3B%20need%20to%20get%3C%2FP%3E%0A%3CP%3EC2%3D2%3C%2FP%3E%0A%3CP%3ED2%3D2%3C%2FP%3E%0A%3CP%3EE2%3D1%3C%2FP%3E%0A%3CP%3EF2%3D0(blank)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-154352%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-154352%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Willy%20for%20the%20response%2C%20Sorry%20if%20I%20didn't%20articulate%20my%20problem%20clearly%3B%3C%2FP%3E%0A%3CP%3EThe%20cells%20in%20which%20I%20require%20values%20are%20C2%2CD2%2CE2%2CF2%3C%2FP%3E%0A%3CP%3Eits%20not%20required%20to%20have%20values%20in%20all%20cells%2C%20if%20we%20take%205%20as%20example%3A%3C%2FP%3E%0A%3CP%3EC2%20has%20to%20take%202%3C%2FP%3E%0A%3CP%3ED2%20has%20to%20take%202%3C%2FP%3E%0A%3CP%3EE2%20has%20to%20take%201%3C%2FP%3E%0A%3CP%3ED2%20can%20be%20blank%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-154164%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-154164%22%20slang%3D%22en-US%22%3E%3CP%3ERemya%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eyour%20cell%20references%20(A2%2C%20B2%2C%20...)%20can't%20be%20correct.%3C%2FP%3E%0A%3CP%3EI%20assume%20%22Category%22%20is%20in%20A1%20and%20%222%22%20is%20in%20F2.%3C%2FP%3E%0A%3CPRE%3E%3DIF(COLUMN()%3D6%2C%24B2-SUM(%24C2%3A%24E2)%2CROUNDUP(%24B2%2F4%2C0))%0A%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-154156%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-154156%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20what%20I%20guess%20you%20want%3C%2FP%3E%0A%3CPRE%3E%3DROUNDUP(IF(B2%3D%24B2%2C%24B2%2C%24B2-SUM(%24C2%3AOFFSET(C2%2C0%2C-1)))%2F(SUMPRODUCT(NOT(%241%3A%241%3D0)*1)-COLUMN(B2))%2C0)%3C%2FPRE%3E%0A%3CP%3EAssume%20that%20you%20enter%20the%20formula%20at%20C2%2C%20and%20copy%20%26amp%3B%20paste%20the%20formula%20to%20the%20cells%20you%20need%20it.%26nbsp%3B%20If%20later%20on%2C%20you%20want%20E%2C%20F%2C%20G%20etc.%2C%20you%20just%20need%20to%20add%20the%20headers%20and%20paste%20the%20formula.%3C%2FP%3E%0A%3CP%3EEg%3A%3C%2FP%3E%0A%3CTABLE%20border%3D%220%22%20width%3D%22384%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2264%22%20height%3D%2217%22%20class%3D%22xl64%22%3ECategory%3C%2FTD%3E%0A%3CTD%20width%3D%2264%22%20class%3D%22xl64%22%3EIncome%3C%2FTD%3E%0A%3CTD%20width%3D%2264%22%20class%3D%22xl64%22%3EA%3C%2FTD%3E%0A%3CTD%20width%3D%2264%22%20class%3D%22xl64%22%3EB%3C%2FTD%3E%0A%3CTD%20width%3D%2264%22%20class%3D%22xl64%22%3EC%3C%2FTD%3E%0A%3CTD%20width%3D%2264%22%20class%3D%22xl64%22%3ED%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20height%3D%2217%22%20class%3D%22xl63%22%3Esomething%3C%2FTD%3E%0A%3CTD%20align%3D%22right%22%20class%3D%22xl63%22%3E6%3C%2FTD%3E%0A%3CTD%20align%3D%22right%22%20class%3D%22xl63%22%3E2%3C%2FTD%3E%0A%3CTD%20align%3D%22right%22%20class%3D%22xl63%22%3E2%3C%2FTD%3E%0A%3CTD%20align%3D%22right%22%20class%3D%22xl63%22%3E1%3C%2FTD%3E%0A%3CTD%20align%3D%22right%22%20class%3D%22xl63%22%3E1%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20height%3D%2217%22%20class%3D%22xl63%22%3Eanother%20thing%3C%2FTD%3E%0A%3CTD%20align%3D%22right%22%20class%3D%22xl63%22%3E5%3C%2FTD%3E%0A%3CTD%20align%3D%22right%22%20class%3D%22xl63%22%3E2%3C%2FTD%3E%0A%3CTD%20align%3D%22right%22%20class%3D%22xl63%22%3E1%3C%2FTD%3E%0A%3CTD%20align%3D%22right%22%20class%3D%22xl63%22%3E1%3C%2FTD%3E%0A%3CTD%20align%3D%22right%22%20class%3D%22xl63%22%3E1%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-935763%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-935763%22%20slang%3D%22en-US%22%3EWilly%2C%3CBR%20%2F%3EIt%E2%80%99s%20a%20while%20since%20you%20posted%20this%20solution%20-%20I%E2%80%99ve%20successfully%20used%20your%20formula%2C%20but%20would%20there%20be%20a%20way%20of%20adjusting%20it%20to%20set%20a%20maximum%20permissible%20value%3F%20I.e.%20in%20your%20example%20A%2C%20B%2C%20C%20etc%20could%20be%20no%20greater%20than%208.%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1016376%22%20slang%3D%22en-US%22%3ERe%3A%20Divide%20a%20quantity%20as%20whole%20number%20among%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1016376%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F97717%22%20target%3D%22_blank%22%3E%40Willy%20Lau%3C%2FA%3E%26nbsp%3BHi%20Willy%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot%20for%20addressing%20this%20issue.%3C%2FP%3E%3CP%3EI%20have%20a%20similar%20requirement.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20distribute%20quantities%20in%201%20cell%20among%2012%20columns%20(all%20months%20of%202020%20for%20a%20forecast)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20applying%20your%20formula%2C%20but%20I%20just%20got%20the%20same%20number%20as%20in%20the%20original%20cell%20in%20the%20cell%20containing%20the%20formula%20but%20fails%20to%20distribute%20it%20among%20the%20rest%2011%20columns.%20Please%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Remya Ramakrishnan
Occasional Contributor

Hello,

Any suggestion for an excel formula to derive A2, B2, C2, D2 values.

 

Eg:

Category Income A B C D
Sal1 11 3 3 3 2

 

if we divide 11 among 4 columns, 2.75 will result in the 4 cells

But I need the excel formula to get the whole number, next integer(3 for 2.75) in all cells.

 

Any response will be helpful. Thanks

17 Replies

This is what I guess you want

=ROUNDUP(IF(B2=$B2,$B2,$B2-SUM($C2:OFFSET(C2,0,-1)))/(SUMPRODUCT(NOT($1:$1=0)*1)-COLUMN(B2)),0)

Assume that you enter the formula at C2, and copy & paste the formula to the cells you need it.  If later on, you want E, F, G etc., you just need to add the headers and paste the formula.

Eg:

Category Income A B C D
something 6 2 2 1 1
another thing 5 2 1 1 1

 

 

Remya,

 

your cell references (A2, B2, ...) can't be correct.

I assume "Category" is in A1 and "2" is in F2.

=IF(COLUMN()=6,$B2-SUM($C2:$E2),ROUNDUP($B2/4,0))

Thank you Willy for the response, Sorry if I didn't articulate my problem clearly;

The cells in which I require values are C2,D2,E2,F2

its not required to have values in all cells, if we take 5 as example:

C2 has to take 2

D2 has to take 2

E2 has to take 1

D2 can be blank

Hello Detlef,

Sorry I didn't explain it right!

I look for auto population of values in C2,D2,E2 and F2 cells.

and if we the quantity to divide is 5,

I  need to get

C2=2

D2=2

E2=1

F2=0(blank)

 

Solution
=MIN(IF(COLUMN()=3,$B2,$B2-SUM($C2:OFFSET(C2,0,-1))), ROUNDUP($B2/(SUMPRODUCT(NOT($1:$1=0)*1)-2),0))

Thank you for the response, I am unable to get the expected results

When I am pasting the formula in C2, it results in circulate reference

 

It is because your sheet keeps my previous version formula.  You may clear C2, D2, E2 and F2.  Then, paste the latest formula to C2.

hmm..Thank you  Willy for the support

 

its solved my issue

 

Thank you!

 

 

 

I need a similar thing with a couple of differences. I need to do the same derive values across variable columns from a number entered into the first column, but i also need to be able to manually override the formula in some cells but have the other cells adjust to the variable. 

 

Example: A1=50, B1:F1 = 10, but if I manually adjust C1 to equal 0, the other 4 cells should auto-adjust to 12.5

The formula should be variable to be copied across 2 cells or 20.

 

Thoughts?

 

Allain

allainm@gmail.com

 

 

 

I need a similar thing with a couple of differences. I need to do the same derive values across variable columns from a number entered into the first column, but i also need to be able to manually override the formula in some cells but have the other cells adjust to the variable. 

 

Example: A1=50, B1:F1 = 10, but if I manually adjust C1 to equal 0, the other 4 cells should auto-adjust to 12.5

The formula should be variable to be copied across 2 cells or 20.

 

Thoughts?

 

Allain

allainm@gmail.com

I am so sorry for the late reply.  I am not sure if the following approach suits your needs.

 

The Formula is

=$A$1/SUMPRODUCT(--(ISFORMULA($B$1:$F$1)))

  

     ABCDEF
150
=TheFormula
=TheFormula
=TheFormula
=TheFormula
=TheFormula

Thanks for trying, I couldn't get that formula to work at all.

 

:-(

May I have your excel version? Does it have IsFormula Function?

I have 2016, I don't know what isFormula function is.

Willy,
It’s a while since you posted this solution - I’ve successfully used your formula, but would there be a way of adjusting it to set a maximum permissible value? I.e. in your example A, B, C etc could be no greater than 8.

Cheers

@Willy Lau Hi Willy,

 

Thanks a lot for addressing this issue.

I have a similar requirement.

 

I need to distribute quantities in 1 cell among 12 columns (all months of 2020 for a forecast)

 

I tried applying your formula, but I just got the same number as in the original cell in the cell containing the formula but fails to distribute it among the rest 11 columns. Please help

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
36 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 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
29 Replies