Hello I have 4 dollar amounts I'm looking to use with a multiplier. The bucket multiplyers would be as follows:
$2,000-$5,000
$5,000-$10,000
$10,000-$50,000
$50,000+
Each one of these values has a (decimal) multiplyer . I'm looking to set up a formula where if the total entered was $25,000 it would multiply that with the multiplyer for the $10,000-$50,000 bucket. Just as eaily if $100,000 was entered it would pull from the entered bucket for $50,000+
Any help is greatly appreciated!
Assume your value to multiply is in Cell A1. Your Formula in B1
=if(and(A1>=2000,A1<5000),Multiplier1 *A1, if(and(A1>=5000,A1<10000),Multiplier2 *A1, if(and(A1>=10000,A1<50000),Mulitplier3 * A1,if(A1>=50000,multiplier4 * A1,0))))
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Amazing stuff. Thank you so much!
Another way:
B8: =A8 * LOOKUP(A8, $A$2:$B$6)
Entia non sunt multiplicanda sine necessitate
This will also work:
Formula:
Please Login or Register to view this content.
Replace the 2,4,6,10 with your multipliers. I also boosted the bottom of each range by 1 as the top value of each range is the bottom value of the next range...can't be in 2 places at once.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks