Hi,
Im trying to create a formula with the following parameters:
lets say you have: 1,000,000,000:
and you get:
$1K for each $1mm (Gross) up to $50mm
$0.75K for each $1mm (Gross) from $51-$150mm
$0.5k for each $1mm (Gross) from $151-$500mm
$0.2k for each $1mm (Gross) from $501mm-$750mm
$0.1k for each $1mm (Gross) thereafter - NO CAP
what would be the formula to see how much the 1,000,000,000 gets?
please help!
Just for clarity, what does mm stand for?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
mm = million
I would further add for clarity, $0.75K actually means $750 for each million between 50,000,001 and 150,000,000, right?
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
yes, that's correct!
Try this formula
Formula:
Please Login or Register to view this content.
Thanks very much!!
Found a misplaced zero
Formula:Please Login or Register to view this content.
ah, the first 50mm? thanks, I was working through the formula now...
does this formula work on a scale? ie. if I change the 1,000,000,000 to 100,000,000, I would expect to get 87,500 but its showing 121,250...
I think I got it, missing 0 on the first min/max. thanks again!!
Yep, a second error, try this
Formula:Please Login or Register to view this content.
Even better, put your upper limits into D1:D5 and refer to them. It reduces possibility of typos and also is more versatile in case you switch the tier values at any point
See attachedFormula:Please Login or Register to view this content.
when i put in 200,000,000 as the reference the output shows 187,500 but i would expect to see 150,000... am i missing something?
It pays to test drive
Formula:Please Login or Register to view this content.
Administrative Note:
Welcome to the forum.
We would love to help you with your query; however it has been brought to our attention the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.
Read this to understand why we (and other sites like us) consider this to be important.
(Note: this requirement is not optional. No help to be offered until the link is provided.)
HTH
Regards, Jeff
i also posted this on:
mrexcel.com/forum/excel-questions/1090977-tiered-formula-help.html[/url]
and an instructor posted for me on:
answers.microsoft.com/en-us/msoffice/forum/all/formula-for-nested-if/ecd8820c-714d-4415-8cdd-a8812fcda01c[/url]
also, thank you everyone for the help, it was much appreciated!
An alternative approach for tiered calcs is below SUMPRODUCT construct as simple to modify tiers (inline arrays can be swapped out for same values held in ranges)
Formula:Please Login or Register to view this content.
key to note is that the final array holds the marginal movement from one tier to the next
e.g. first tier is @ 0.001% whilst second tier drops to @ 0.00075% - a movement of -0.00025% etc...
edit: didn't read the x-posts so above just a regurgitation of the final response provided on the microsoft site
Last edited by XLent; 03-16-2019 at 06:46 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks