SOLVED
Home

Pricing sheet using ranges of quantity discounts

%3CLINGO-SUB%20id%3D%22lingo-sub-674701%22%20slang%3D%22en-US%22%3EPricing%20sheet%20using%20ranges%20of%20quantity%20discounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-674701%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20building%20a%20pricing%20sheet%20and%20need%20the%20below%20table%20to%20show%20the%20cumulative%20pricing%20based%20on%20the%20ranges.%20%26nbsp%3BAnybody%20any%20idea%20to%20do%20this%20without%20a%20bunch%20of%20nested%20IF's%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3ERange%20Start%3C%2FTD%3E%3CTD%3ERange%20End%3C%2FTD%3E%3CTD%3EStandard%20discount%3C%2FTD%3E%3CTD%3EList%20Price%3C%2FTD%3E%3CTD%3EDiscounted%20price%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E49%3C%2FTD%3E%3CTD%3E0%2C00%25%3C%2FTD%3E%3CTD%3E%E2%82%AC%2030%2C00%3C%2FTD%3E%3CTD%3E%E2%82%AC%2030%2C000%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E50%3C%2FTD%3E%3CTD%3E99%3C%2FTD%3E%3CTD%3E5%2C00%25%3C%2FTD%3E%3CTD%3E%E2%82%AC%2030%2C00%3C%2FTD%3E%3CTD%3E%E2%82%AC%2028%2C500%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E100%3C%2FTD%3E%3CTD%3E499%3C%2FTD%3E%3CTD%3E10%2C00%25%3C%2FTD%3E%3CTD%3E%E2%82%AC%2030%2C00%3C%2FTD%3E%3CTD%3E%E2%82%AC%2027%2C000%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E500%3C%2FTD%3E%3CTD%3E2.499%3C%2FTD%3E%3CTD%3E15%2C00%25%3C%2FTD%3E%3CTD%3E%E2%82%AC%2030%2C00%3C%2FTD%3E%3CTD%3E%E2%82%AC%2025%2C500%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2.500%3C%2FTD%3E%3CTD%3E4.999%3C%2FTD%3E%3CTD%3E20%2C00%25%3C%2FTD%3E%3CTD%3E%E2%82%AC%2030%2C00%3C%2FTD%3E%3CTD%3E%E2%82%AC%2024%2C000%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5.000%3C%2FTD%3E%3CTD%3E9.999%3C%2FTD%3E%3CTD%3E25%2C00%25%3C%2FTD%3E%3CTD%3E%E2%82%AC%2030%2C00%3C%2FTD%3E%3CTD%3E%E2%82%AC%2022%2C500%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10.000%3C%2FTD%3E%3CTD%3E14.999%3C%2FTD%3E%3CTD%3E30%2C00%25%3C%2FTD%3E%3CTD%3E%E2%82%AC%2030%2C00%3C%2FTD%3E%3CTD%3E%E2%82%AC%2021%2C000%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E15.000%3C%2FTD%3E%3CTD%3E19.999%3C%2FTD%3E%3CTD%3E35%2C00%25%3C%2FTD%3E%3CTD%3E%E2%82%AC%2030%2C00%3C%2FTD%3E%3CTD%3E%E2%82%AC%2019%2C500%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E20.000%3C%2FTD%3E%3CTD%3E100.000%3C%2FTD%3E%3CTD%3E40%2C00%25%3C%2FTD%3E%3CTD%3E%E2%82%AC%2030%2C00%3C%2FTD%3E%3CTD%3E%E2%82%AC%2018%2C000%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EQuantity%3C%2FTD%3E%3CTD%3ETotal%3C%2FTD%3E%3CTD%3EUnit%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EExample1%3A%3C%2FTD%3E%3CTD%3E75%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%E2%82%AC%202.211%2C000%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%E2%82%AC%2029%2C480%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EExample2%3A%3C%2FTD%3E%3CTD%3E2550%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%E2%82%AC%2065.919%2C000%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%E2%82%AC%2025%2C851%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20example%20is%26nbsp%3Bthe%20first%2049%20licenses%20%4030%E2%82%AC%2C%20the%20next%2075%20-%2049%20%3D%2026%20%40%2028%2C5%E2%82%AC%3C%2FP%3E%3CP%3EThe%20second%20example%20uses%20same%20logic%2C%20within%20each%20range%20the%20number%20of%20license%20at%20their%20rate%20%2B%20last%2050%20%4024%E2%82%AC%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20for%20a%20magic%20formula%20in%20the%20Total%20cell%20in%20bold%20to%20perform%20the%20calculation%20based%20on%20the%20quantity%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-674701%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-675885%22%20slang%3D%22en-US%22%3ERe%3A%20Pricing%20sheet%20using%20ranges%20of%20quantity%20discounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675885%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F355833%22%20target%3D%22_blank%22%3E%40bagudd%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3EIf%20change%20the%20start%20of%20the%20first%20range%20on%201%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20508px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117372i1082DDD85A42BFED%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewhen%20the%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((%20%20(%24C%243%3A%24C%2411-%24B%243%3A%24B%2411%2B1)*(%24B14%26gt%3B%24C%243%3A%24C%2411)%2B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(%24B14-%24B%243%3A%24B%2411%2B1)*(%24B14%26lt%3B%3D%24C%243%3A%24C%2411)*(%24B14%26gt%3B%24B%243%3A%24B%2411))*%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%24E%243%3A%24E%2411*(1-%24D%243%3A%24D%2411)%20%20)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-675951%22%20slang%3D%22en-US%22%3ERe%3A%20Pricing%20sheet%20using%20ranges%20of%20quantity%20discounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675951%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%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%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBig%20thanks%20for%20the%20feedback.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20see%20an%20error%20when%20entering%20the%20tilting%20values%2C%20like%2050%2C%20that%20shows%20the%20same%20value%20as%20for%2049.%20%26nbsp%3BSame%20goes%20for%20100%2C%20500%20etc.%3C%2FP%3E%3CP%3EAny%20idea%20what%20the%20fix%20could%20be%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-676195%22%20slang%3D%22en-US%22%3ERe%3A%20Pricing%20sheet%20using%20ranges%20of%20quantity%20discounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-676195%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F355833%22%20target%3D%22_blank%22%3E%40bagudd%3C%2FA%3E%26nbsp%3B%2C%20please%20check%20this%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((%20%20(%24C%243%3A%24C%2411-%24B%243%3A%24B%2411%2B1)*(B14%26gt%3B%24C%243%3A%24C%2411)%2B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(B14-%24B%243%3A%24B%2411%2B1)*(B14%26lt%3B%3D%24C%243%3A%24C%2411)*(B14%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%26gt%3B%3D%3C%2FSTRONG%3E%3C%2FFONT%3E%24B%243%3A%24B%2411))*%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%24E%243%3A%24E%2411*(1-%24D%243%3A%24D%2411)%20%20)%3C%2FPRE%3E%0A%3CP%3E%22more%22%20is%20changed%20on%20%22more%20or%20equal%20to%22%2C%20in%20red%20above%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-676945%22%20slang%3D%22en-US%22%3ERe%3A%20Pricing%20sheet%20using%20ranges%20of%20quantity%20discounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-676945%22%20slang%3D%22en-US%22%3EGreat%2C%20thank%20you%20for%20the%20help!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677041%22%20slang%3D%22en-US%22%3ERe%3A%20Pricing%20sheet%20using%20ranges%20of%20quantity%20discounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677041%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F355833%22%20target%3D%22_blank%22%3E%40bagudd%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
bagudd
New Contributor

I'm building a pricing sheet and need the below table to show the cumulative pricing based on the ranges.  Anybody any idea to do this without a bunch of nested IF's?

 

Range StartRange EndStandard discountList PriceDiscounted price
0490,00%€ 30,00€ 30,000
50995,00%€ 30,00€ 28,500
10049910,00%€ 30,00€ 27,000
5002.49915,00%€ 30,00€ 25,500
2.5004.99920,00%€ 30,00€ 24,000
5.0009.99925,00%€ 30,00€ 22,500
10.00014.99930,00%€ 30,00€ 21,000
15.00019.99935,00%€ 30,00€ 19,500
20.000100.00040,00%€ 30,00€ 18,000

 

 QuantityTotalUnit
Example1:75€ 2.211,000€ 29,480
Example2:2550€ 65.919,000€ 25,851

 

The first example is the first 49 licenses @30€, the next 75 - 49 = 26 @ 28,5€

The second example uses same logic, within each range the number of license at their rate + last 50 @24€

 

I'm looking for a magic formula in the Total cell in bold to perform the calculation based on the quantity cell.

 

Thanks!

 
 
5 Replies

@bagudd ,

If change the start of the first range on 1

image.png

when the formula could be

=SUMPRODUCT((  ($C$3:$C$11-$B$3:$B$11+1)*($B14>$C$3:$C$11)+
               ($B14-$B$3:$B$11+1)*($B14<=$C$3:$C$11)*($B14>$B$3:$B$11))*
             $E$3:$E$11*(1-$D$3:$D$11)  )

 

Hi @Sergei Baklan ,

 

Big thanks for the feedback. 

I do see an error when entering the tilting values, like 50, that shows the same value as for 49.  Same goes for 100, 500 etc.

Any idea what the fix could be?

 

Thanks

 
 
 
 
 
 
 
 
 
Solution

@bagudd , please check this

=SUMPRODUCT((  ($C$3:$C$11-$B$3:$B$11+1)*(B14>$C$3:$C$11)+
                (B14-$B$3:$B$11+1)*(B14<=$C$3:$C$11)*(B14>=$B$3:$B$11))*
             $E$3:$E$11*(1-$D$3:$D$11)  )

"more" is changed on "more or equal to", in red above

Great, thank you for the help!
Highlighted

@bagudd , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 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
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies