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

Highlighted
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)
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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 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
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies