Forum Discussion

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%,...

Formulas and Functions

m_tarler
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)

m_tarler

Silver Contributor

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)