Need support with calculation logic

[raviansal]

Hi Team,


I am trying to create a price calculator. It is a bit tough and i am not good at these complex logics. kind of subscription model. Can you please help.

Logic :
1. Select a product ( there are 10 different products)
2. Select the type of agreement ( three are 5 different options)
3. Number of products ( min : 1; max : 999)
4. Age of the device: can be 1 to 5 ( basically 1 year old ; 2 year old ; 3....)
5. Number of agreement years : 1 to 5 years

there is a discount model : every agreement year adds a 5% discount but age adds 5%.

Here is some demo data

List of pricing

Calculator:

Final drop down:

I am still learning the best way to do it. Not sure which logic can make it work and does not over complicate. specially that the pricing can change and if it is not dynamic it will fail.
Happy to get some directions and help.

Best,
Ravi Ansal

 

[SanjayGMusafir]

Hi Team,


I am trying to create a price calculator. It is a bit tough and i am not good at these complex logics. kind of subscription model. Can you please help.

Logic :
1. Select a product ( there are 10 different products)
2. Select the type of agreement ( three are 5 different options)
3. Number of products ( min : 1; max : 999)
4. Age of the device: can be 1 to 5 ( basically 1 year old ; 2 year old ; 3....)
5. Number of agreement years : 1 to 5 years

there is a discount model : every agreement year adds a 5% discount but age adds 5%.

Here is some demo data

List of pricing
View attachment 70741

Calculator:
View attachment 70742

Final drop down:

View attachment 70743

I am still learning the best way to do it. Not sure which logic can make it work and does not over complicate. specially that the pricing can change and if it is not dynamic it will fail.
Happy to get some directions and help.

Best,
Ravi Ansal

First of all you are doing 2 things wrong -
First - Table Structure of List of Pricing
Second - Table Structure of Discount as per the years

If you can do above two things right you can pull that information using Data validation and XLOOKUP and calculate the desired result instantly.