Still nothing. I actually get the same results as the previous formula. Still anything over 500,000 doesn't add at the proper tier.
I have cleaned the spreadsheet and attached it for your reference. Included is the SUMPRODUCT formula that works - I've been using it as a cross-check.
As you can see, anything under 500k matches. Once you put in 501,000 - they're off. I know it's not calculating at the right tier because if you look at the 'Calculations' sheet, that 1000 is calculated at $3.31, which is B30, and it should be calculating at $2.78, which is B31.
Strange, because it definitely references B31 in the formula...
Ok you had some places where you didn't reference the Calculations tab, and I had some places where I used colons instead of minus signs...
Try:
=SUM(MIN(CEILING(Purchase_Price,1000),Calculations!A30)*Calculations!B29,MAX(0,MIN(CEILING(Purchase_Price,1000)-Calculations!A30,(Calculations!A31-Calculations!A30)))*Calculations!B30,MAX(0,MIN(CEILING(Purchase_Price,1000)-Calculations!A31,(Calculations!A32-Calculations!A31)))*Calculations!B31,MAX(0,MIN(CEILING(Purchase_Price,1000)-Calculations!A32,(Calculations!A33-Calculations!A32)))*Calculations!B32,MAX(0,MIN(CEILING(Purchase_Price,1000)-Calculations!A33,(Calculations!A34-Calculations!A33)))*Calculations!B33,MAX(0,(CEILING(Purchase_Price,1000)-Calculations!A34))*Calculations!B34)/1000
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
It works perfectly. Thank you so much (again)!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks