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
Highlighted
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)
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
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies