SOLVED
Home

Need Help with a Formula for division with multiple variables.

%3CLINGO-SUB%20id%3D%22lingo-sub-712079%22%20slang%3D%22en-US%22%3ENeed%20Help%20with%20a%20Formula%20for%20division%20with%20multiple%20variables.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712079%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22content%22%3E%3CDIV%3E%3CBLOCKQUOTE%3EI%20need%20help%20with%20a%20formula%20for%3A%20with%20the%20variable%20that%20only%20when%20%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3EM%3C%2FFONT%3E%3C%2FSTRONG%3E%20is%20over%20%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E250%3C%2FSTRONG%3E%3C%2FFONT%3E%20to%20divide%20%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3EM%3C%2FFONT%3E%3C%2FSTRONG%3E%20by%20%3CSTRONG%3E2%3C%2FSTRONG%3E%20to%20display%20it%20in%20N%20%26amp%3B%20O%2C%20when%20%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3EM%3C%2FFONT%3E%3C%2FSTRONG%3E%20is%20over%20%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E500%3C%2FSTRONG%3E%3C%2FFONT%3E%20to%20divide%20%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3EM%3C%2FFONT%3E%3C%2FSTRONG%3E%20by%20%3CSTRONG%3E3%3C%2FSTRONG%3E%20to%20display%20it%20in%20N%2C%20O%2C%20%26amp%3B%20P%2C%20when%20%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3EM%3C%2FFONT%3E%3C%2FSTRONG%3E%20is%20over%20%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E750%3C%2FSTRONG%3E%3C%2FFONT%3E%20to%20divide%20%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3EM%3C%2FFONT%3E%3C%2FSTRONG%3E%20by%20%3CSTRONG%3E4%3C%2FSTRONG%3E%20to%20display%20it%20in%20N%2C%20O%2C%20P%2C%20%26amp%3B%20Q.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20assuming%20that%20this%20formula(s)%20would%20need%20to%20be%20in%20columns%20N%2C%20O%2C%20P%2C%20%26amp%3B%20Q%20but%20I%20do%20not%20know%20how%20to%20do%20a%20formula%20with%20the%20variables%20above%20so%20that%20when%20%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3EM%3C%2FFONT%3E%3C%2FSTRONG%3E%20is%20between%20%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E250%3C%2FSTRONG%3E%3C%2FFONT%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E-500%3C%2FSTRONG%3E%3C%2FFONT%3E%20it%20only%20displays%20in%20%3CSTRONG%3E2%20columns%3C%2FSTRONG%3E%2C%20is%20between%20%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E500-750%3C%2FSTRONG%3E%3C%2FFONT%3E%20it%20only%20displays%20in%20%3CSTRONG%3E3%20columns%2C%3C%2FSTRONG%3E%20and%20is%20between%20%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E750%2B%3C%2FSTRONG%3E%3C%2FFONT%3E%20it%20displays%20in%20%3CSTRONG%3E4%20columns%3C%2FSTRONG%3E.%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20what%20I%20know%20about%20formulas%2C%20the%20formulas%20that%20I%20currently%20in%20Row%204%20in%20the%20spreadsheet%20attached%2C%20only%20has%20%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3EM%3C%2FFONT%3E%3C%2FSTRONG%3E%20is%20dividing%20in%204%20if%20it%20over%20250.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20anyone%20could%20help%20with%20this%2C%20I%20would%20really%20appreciate%20it!!!!%3C%2FBLOCKQUOTE%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-712079%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-712141%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20a%20Formula%20for%20division%20with%20multiple%20variables.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712141%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F348240%22%20target%3D%22_blank%22%3E%40afprinter%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20could%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3DIF(COLUMN()-COLUMN(%24M3)%26gt%3B(INT(%24M3%2F250)%2B1)%2C0%2C%24M3%2F(INT(%24M3%2F250)%2B1))%3C%2FPRE%3E%0A%3CP%3Ein%20N3%20and%20drag%20it%20to%20another%20cells%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-712201%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20a%20Formula%20for%20division%20with%20multiple%20variables.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712201%22%20slang%3D%22en-US%22%3EIf%20I%20copy%20this%20formula%20into%20N3%20and%20the%20other%20cells%2C%20I%20get%20206%20in%20N%20and%200%20in%20O%2C%20P%2C%20%26amp%3B%20Q.%3CBR%20%2F%3EIt%20works%20for%20Row%204.%3CBR%20%2F%3EIs%20there%20a%20way%20to%20get%20N3%20to%20not%20display%20206%20where%20it%20is%20under%20250%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-712210%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20a%20Formula%20for%20division%20with%20multiple%20variables.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712210%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F348240%22%20target%3D%22_blank%22%3E%40afprinter%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%3C%2FP%3E%0A%3CPRE%3E%3DIF(COLUMN()-COLUMN(%24M3)%26gt%3B(INT(%24M3%2F250)%2B1)%2C0%2C%24M3%2F(INT(%24M3%2F250)%2B1))*(%24M3%26gt%3B%3D250)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-712215%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20a%20Formula%20for%20division%20with%20multiple%20variables.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712215%22%20slang%3D%22en-US%22%3EThat%20works%20awesome!%3CBR%20%2F%3E%3CBR%20%2F%3ENow%2C%20is%20it%20possible%20to%20add%20Column%20I%20in%20the%20formula%20for%20N%20only%20when%20N%20has%20a%20value%20greater%20than%20zero%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-714126%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20a%20Formula%20for%20division%20with%20multiple%20variables.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-714126%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F348240%22%20target%3D%22_blank%22%3E%40afprinter%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EValue%20in%20N%20is%20greater%20than%20zero%20if%20only%20value%20in%20M%20is%20greater%20of%20equal%20than%20250.%20With%20checking%20what%20we%20are%20in%20column%20N%20the%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3D(IF(COLUMN()-COLUMN(%24M3)%26gt%3B(INT(%24M3%2F250)%2B1)%2C0%2C%24M3%2F(INT(%24M3%2F250)%2B1))%2B%24I3*(COLUMN()%3DCOLUMN(%24N%241)))*(%24M3%26gt%3B%3D250)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-714141%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20a%20Formula%20for%20division%20with%20multiple%20variables.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-714141%22%20slang%3D%22en-US%22%3EThank%20you%20SOOOO%20much!!!!%20I%20could%20never%20have%20done%20this!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-714379%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20a%20Formula%20for%20division%20with%20multiple%20variables.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-714379%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F348240%22%20target%3D%22_blank%22%3E%40afprinter%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
afprinter
Occasional Contributor
I need help with a formula for: with the variable that only when M is over 250 to divide M by 2 to display it in N & O, when M is over 500 to divide M by 3 to display it in N, O, & P, when M is over 750 to divide M by 4 to display it in N, O, P, & Q.

I am assuming that this formula(s) would need to be in columns N, O, P, & Q but I do not know how to do a formula with the variables above so that when M is between 250-500 it only displays in 2 columns, is between 500-750 it only displays in 3 columns, and is between 750+ it displays in 4 columns.

With what I know about formulas, the formulas that I currently in Row 4 in the spreadsheet attached, only has M is dividing in 4 if it over 250.

If anyone could help with this, I would really appreciate it!!!!
7 Replies

@afprinter 

 

It could be like

=IF(COLUMN()-COLUMN($M3)>(INT($M3/250)+1),0,$M3/(INT($M3/250)+1))

in N3 and drag it to another cells

If I copy this formula into N3 and the other cells, I get 206 in N and 0 in O, P, & Q.
It works for Row 4.
Is there a way to get N3 to not display 206 where it is under 250?
Solution

@afprinter 

When

=IF(COLUMN()-COLUMN($M3)>(INT($M3/250)+1),0,$M3/(INT($M3/250)+1))*($M3>=250)
Highlighted
That works awesome!

Now, is it possible to add Column I in the formula for N only when N has a value greater than zero?

@afprinter 

Value in N is greater than zero if only value in M is greater of equal than 250. With checking what we are in column N the formula could be

=(IF(COLUMN()-COLUMN($M3)>(INT($M3/250)+1),0,$M3/(INT($M3/250)+1))+$I3*(COLUMN()=COLUMN($N$1)))*($M3>=250)

 

Thank you SOOOO much!!!! I could never have done this!

@afprinter , you are welcome, glad to help

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