SOLVED
Home

Need help with a complicated formula

%3CLINGO-SUB%20id%3D%22lingo-sub-775327%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20a%20complicated%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775327%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20cant%20seem%20to%20figure%20out%20how%20to%20make%20this%20work.%26nbsp%3B%20i%20have%202%20formulas%20i%20need%20to%20use%20in%20a%20sheet%20i%20am%20making.%26nbsp%3B%20column%20A%20will%20have%203%20options%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3EType%3C%2FTD%3E%3CTD%3EWeight%3C%2FTD%3E%3CTD%3ETotal%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CP%3E1%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E500%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E2%3C%2FP%3E%3C%2FTD%3E%3CTD%3E500%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E500%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Eformula%20will%20be%20the%20same%20for%20type%201%20and%202%3C%2FP%3E%3CP%3E%3DIF((A1*0.033)%26gt%3B50%2C((50))%2CIF((A1*0.033)%26lt%3B15%2C((15))%2C((A1*0.033))))%3C%2FP%3E%3CP%3Eand%203%20will%20be%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF((FA1*0.033)%26gt%3B35%2C((35))%2CIF((A1*0.033)%26lt%3B10%2C((10))%2C((A1*0.033))))%3C%2FP%3E%3CP%3Ei%20need%20to%20make%20the%20last%20column%20recognize%20which%20formula%20to%20multiply%20weight%20by%20depending%20on%20whether%20its%201%2C%202%20or%203%20in%20first%20column%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-775327%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-775356%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20complicated%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775356%22%20slang%3D%22en-US%22%3Eshould%20say%20B1%3CBR%20%2F%3Eif%20type1%20or%202%3CBR%20%2F%3E%3DIF((B1*0.033)%26gt%3B50%2C((50))%2CIF((B1*0.033)%26lt%3B15%2C((15))%2C((B1*0.033))))%3CBR%20%2F%3Eif%20type%203%3CBR%20%2F%3E%3DIF((B1*0.033)%26gt%3B35%2C((35))%2CIF((B1*0.033)%26lt%3B10%2C((10))%2C((B1*0.033))))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-775394%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20complicated%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775394%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382786%22%20target%3D%22_blank%22%3E%40Jethro323%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3E%3DIF(A1%26lt%3B3%2C%0A%20%20MIN(50%2CMAX(15%2CB1*0.033))%2C%0A%20%20MIN(35%2CMAX(10%2CB1*0.033))%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776267%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20complicated%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776267%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bthank%20you%20so%20much%20it%20works%20perfectly%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776535%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20complicated%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776535%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382786%22%20target%3D%22_blank%22%3E%40Jethro323%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome.%20If%20you%20don't%20mind%20I%20moved%20%22best%20response%22%20on%20the%20answer%3C%2FP%3E%3C%2FLINGO-BODY%3E
Jethro323
New Contributor

So I cant seem to figure out how to make this work.  i have 2 formulas i need to use in a sheet i am making.  column A will have 3 options 

TypeWeightTotal

1

500

 

2

500 
3500 

formula will be the same for type 1 and 2

=IF((A1*0.033)>50,((50)),IF((A1*0.033)<15,((15)),((A1*0.033))))

and 3 will be 

=IF((FA1*0.033)>35,((35)),IF((A1*0.033)<10,((10)),((A1*0.033))))

i need to make the last column recognize which formula to multiply weight by depending on whether its 1, 2 or 3 in first column

4 Replies
should say B1
if type1 or 2
=IF((B1*0.033)>50,((50)),IF((B1*0.033)<15,((15)),((B1*0.033))))
if type 3
=IF((B1*0.033)>35,((35)),IF((B1*0.033)<10,((10)),((B1*0.033))))
Solution

@Jethro323 

It could be

=IF(A1<3,
  MIN(50,MAX(15,B1*0.033)),
  MIN(35,MAX(10,B1*0.033))
)

 

@Sergei Baklan thank you so much it works perfectly

@Jethro323 , you are welcome. If you don't mind I moved "best response" on the answer

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies