Greetings,
New member here, ran across this website researching Excel and must say a terrific resource, thank you for providing it.
I’ve been trying to solve this one for awhile now and hoping the community can help me.
I’m building a spreadsheet where you key in a dollar amount (Ex: $100,000) and depending on how much it is, a fee is charged based on a pricing structure (Ex: For the first $10,000 you’re charged $1.00 per thousand, then on the next $30,000 you’re charged $.80 per thousand, then on the next $50,000 you’re charged $.50 per thousand, and finally the next $100,000 you’re charged $.30 per thousand.
Calculation:
First $10,000=$10.00 ($1.00 per thousand)
Next $30,000= $24.00 ($.80 per thousand)
Next $50,000= $25.00 ($.50 per thousand)
Next $100,000= $3.00 ($.30 per thousand)
The Resulting Answer:
Total = $62.00 in fees for $100,000. The $100,000 would be the variable.
The max charge could be $89 (Because you maxed out all of the tiers with a $190,000 variable)
I’ve tried SUMPRODUCT but not sure how to make that formula jump from tier-to-tier without it calculating the full $100,000 on each tier when it should only calculate $10,000 at Tier 1, the next $30,000 on Tier 2, the next $50,000 on Tier 3, and the last $10,000 at the Tier 4 $100,000 Rate.
Can this great community assist me? Let me know if more information is needed.
Signed,
John
New member here, ran across this website researching Excel and must say a terrific resource, thank you for providing it.
I’ve been trying to solve this one for awhile now and hoping the community can help me.
I’m building a spreadsheet where you key in a dollar amount (Ex: $100,000) and depending on how much it is, a fee is charged based on a pricing structure (Ex: For the first $10,000 you’re charged $1.00 per thousand, then on the next $30,000 you’re charged $.80 per thousand, then on the next $50,000 you’re charged $.50 per thousand, and finally the next $100,000 you’re charged $.30 per thousand.
Calculation:
First $10,000=$10.00 ($1.00 per thousand)
Next $30,000= $24.00 ($.80 per thousand)
Next $50,000= $25.00 ($.50 per thousand)
Next $100,000= $3.00 ($.30 per thousand)
The Resulting Answer:
Total = $62.00 in fees for $100,000. The $100,000 would be the variable.
The max charge could be $89 (Because you maxed out all of the tiers with a $190,000 variable)
I’ve tried SUMPRODUCT but not sure how to make that formula jump from tier-to-tier without it calculating the full $100,000 on each tier when it should only calculate $10,000 at Tier 1, the next $30,000 on Tier 2, the next $50,000 on Tier 3, and the last $10,000 at the Tier 4 $100,000 Rate.
Can this great community assist me? Let me know if more information is needed.
Signed,
John
