Thread: Making a logic formula for calculating a customer's deposit.

Hi Everyone and thanks for the help!

I'm new to this forum and I don't know how i'm going to explain my problem, but i'll do my best.

I'm trying to calculate how much of a deposit a customer will make depending on the sale price of an item.

Here are the details:

Deposit amount = (Final Price- Base Price)*50%

I would like the customer to make this calculation themselves by having a webpage with a textbox and a button that uses the formula. The customer would simply INPUT the Final Price amount and get an OUTPUT of the Deposit Amount.

The problem is that I don't want the customer to be able to figure out the Base Price. If a customer enters in various numbers into the text box they can figure out the Base Price when the output becomes zero. E.g. ($5,000-$5,000)*50%= 0.

In order to mask the Base Price I was trying to come up with a formula that will only show a zero output when the customer inputs a Final Price of zero. So in other words, the formula only needs to make accurate calculations beyond a minimum output of say $400.

This is what I have so far (and this is where i'm truly stuck):
By the way, I was using Excel to calculate this. That's why it's in this format.

IF((Final Price- Base Price)*50%)>400, (Final Price- Base Price)*50%, if not then the output will slowly taper down to zero and only be zero when the input is zero.

I wrote the last part in plain english because that's wehre i'm having the most trouble expressing it as a mathematical formula.

Once again the goal is to mask the inner workings of the formula so that the customer cannot easily figure out the Base Price when they're entering various Inputs. The only way I can figure out how to mask this is to only make the formula accurate beyond a certain output. Maybe there's another way of masking it though.

Let me know if you need more information... Logic was never my strong suit (or even math) so this would be a big help to me.


Thanks,
James

In order to mask the Base Price I was trying to come up with a formula that will only show a zero output when the customer inputs a Final Price of zero.

IF((Final Price- Base Price)*50%)>400, (Final Price- Base Price)*50%, if not then the output will slowly taper down to zero and only be zero when the input is zero.

I think there is some confusion concerning when the output is 0. As you wrote in the original post, when the base price is $5000, the output $0 happens when the input (final price) is also $5000. When the input is 0, the output is -2500.

Concerning "slowly tapering down to zero", the expression (Final Price- Base Price)*50% tapers down from 400 to 0 when the Final Price changes from 5800 to 5000 (again assuming the Base Price is 5000). So, when the Final Price >= 5800, you want to use this formula, but what behavior do you want when the Final Price < 5800?

It may also make sense to ask this question in the "Business Math" section of this site.

Thanks for the advice @Emakarov!

After reading your post I came up with this:

IF(Final Price>=5800, (Final Price-Base Price)*0.5,Final Price*0.05)

It's does a good job masking the Base Price, but its a little obvious when the results start using the Final Price*0.5... I guess there's no other way to mask the base price though. I might try changing my percentages from 50% to something like 49.38% and from 5% to 4.85% just to make the results/outputs a little more difficult for the customer to make any sense of.

If anybody has any other ideas on how to mask the Base Price let me know. If not, I think I'm happy with this formula.

Thanks again!

-James