Home

Logical Formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-543992%22%20slang%3D%22en-US%22%3ELogical%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-543992%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20on%20a%20project%20and%20I%20am%20needing%20to%20reference%20three%20different%20ranges(prices)%20of%20cells%20based%20on%20pricing%20levels%20below.%20I%20have%20seven%20different%20prices%2Fparts%20I%20need%20to%20have%20a%20formula%20to%20add%20together%20and%20total%20and%20when%20it%20hits%20%242501-%2410%2C000%20it%20needs%20to%20use%20the%20second%20range%20of%20prices%2C%20and%20the%20third%20range%20of%20pricing%20for%20total%26nbsp%3B%40%20and%20over%20%2425%2C000%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%26gt%3B2500%3C%2FP%3E%3CP%3E%26gt%3B10%2C000%26lt%3B%3C%2FP%3E%3CP%3E%3D%26lt%3B25%2C000%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EJoshua%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-543992%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-544774%22%20slang%3D%22en-US%22%3ERe%3A%20Logical%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-544774%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F337520%22%20target%3D%22_blank%22%3E%40jmorr08u%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20stated%20%22%3CSPAN%3EI%20need%20to%20have%20a%20formula%20to%20add%20together%20and%20total%20and%20when%20%3CSTRONG%3Eit%3C%2FSTRONG%3E%20hits%20%242501-%2410%2C000%20%3CSTRONG%3Eit%3C%2FSTRONG%3E%20needs%20to%20use%20the%20second%20range%20of%20prices%2C%20and%20the%20third%20range%20of%20pricing%20for%20%3CSTRONG%3Etotal%3C%2FSTRONG%3E%26nbsp%3B%40%20and%20over%20%2425%2C000%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EWhat%20does%20%22%3CSTRONG%3Eit%3C%2FSTRONG%3E%22%20refer%20to%3F%20%3CSTRONG%3EIt%3C%2FSTRONG%3E%20cannot%20refer%20to%20%3CSTRONG%3Etotal%3C%2FSTRONG%3E%20in%20your%20sample%20file%20because%20%3CSTRONG%3Eit%3C%2FSTRONG%3E%20will%20result%20in%20%3CSTRONG%3Ecircular%20reference%20error%3C%2FSTRONG%3E.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-548563%22%20slang%3D%22en-US%22%3ERe%3A%20Logical%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-548563%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F337520%22%20target%3D%22_blank%22%3E%40jmorr08u%3C%2FA%3E%26nbsp%3B%2C%20that%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DG2*IF(SUMPRODUCT(%24G%242%3A%24G%248*%24L%242%3A%24L%248)%26gt%3B%24L%241%2C%24K2%2C%0A%20%20%20%20%20%20IF(SUMPRODUCT(%24G%242%3A%24G%248*%24M%242%3A%24M%248)%26gt%3B%24M%241%2C%24L2%2C%24M2))%3C%2FPRE%3E%0A%3CP%3Ein%20I2%20and%20drag%20it%20down.%20I%20removed%20multiplier%20100%20in%20initial%20formula%20since%20it%20wasn't%20explained%20how%20to%20use%20it%2C%20Cost%3DQty*Price%20here%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
jmorr08u
Occasional Visitor

Hello, 

 

I am working on a project and I am needing to reference three different ranges(prices) of cells based on pricing levels below. I have seven different prices/parts I need to have a formula to add together and total and when it hits $2501-$10,000 it needs to use the second range of prices, and the third range of pricing for total @ and over $25,000 

 

=>2500

>10,000<

=<25,000

 

Thank you,

Joshua 

2 Replies

@jmorr08u 

You stated "I need to have a formula to add together and total and when it hits $2501-$10,000 it needs to use the second range of prices, and the third range of pricing for total @ and over $25,000"

What does "it" refer to? It cannot refer to total in your sample file because it will result in circular reference error

 

@jmorr08u , that could be

=G2*IF(SUMPRODUCT($G$2:$G$8*$L$2:$L$8)>$L$1,$K2,
      IF(SUMPRODUCT($G$2:$G$8*$M$2:$M$8)>$M$1,$L2,$M2))

in I2 and drag it down. I removed multiplier 100 in initial formula since it wasn't explained how to use it, Cost=Qty*Price here

 

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 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
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies