Forum Discussion

j_mig's avatar
j_mig
Copper Contributor
Dec 04, 2024
Solved

update price applying percentages by sections

I have an Excel table with prices. I need to calculate the price that would come out after applying the following sections to said amount. If the price exceeds the amount of 4500 between 1% and 10%,...
  • m_tarler's avatar
    Dec 04, 2024

    assume price is in A1 then

    =min(A1,4500*101%)
    +min( max(A1-4500*101% , 0) , 4500*10%) *0.92%
    +min( max(A1-4500*110% , 0) , 4500*50%) *1%
    +max(A1-4500*150% , 0) *1.17%

    you could turn this into a LAMBDA function to make thing cleaner/easier.  To do this you go to formulas tab in the menu/ribbon and select Name Manager and then select New and then:

    Name: PriceAdj

    Refers to: = LAMBDA( p, min(p,4500*101%)+min( max(p-4500*101% , 0) , 4500*10%) *0.92%+min( max(p-4500*110% , 0) , 4500*50%) *1%+max(p-4500*150% , 0) *1.17%)

    then you just need to use:

    PriceAdj(A1)