Forum Discussion
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%, 0.92% is applied to the excess.
If it additionally exceeds the amount of 4500 between 10% and 50%, 1% is applied to the additional excess
If it additionally exceeds the amount of 4500 by more than 50%, 1.17% is applied to the additional excess.
The result must be the sum of the excess of each section that corresponds to each price.
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)
2 Replies
Hi m_tarler:
Thanks for your help and your time.
Where can i get more information about Lamda function?
It's new for me and I don't know that exists.
regards
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)
