I have to split the Variable range into tiers. I would like the Tiers to automatically generate based on the Peak and Base, which would be independently entered. Please help! Thank you!
(Input)
Peak Capacity 550
Base Capacity 300
(Output)
5 Tiers would be:
Tier 1 = 300 - 350
Tier 2 = 351 - 400
Tier 3 = 401 - 450
Tier 4 = 451 - 500
Tier 5 = 501 - 550
Last edited by OilAndGasMan1984; 06-19-2016 at 07:32 PM.
With a lookup table in columns D:E, data in column A and this formula in B2 and copied down.Formula:
Please Login or Register to view this content.
Row\Col Data Tier 1 Tier 1 Tier 2 Tier 3 Tier 3 Tier 4 Tier 4 Tier 3 Tier 5 Tier 4 Tier 2 Tier 3 Tier 2 Tier 5 Tier 1 Tier 1 Tier 5 Tier 5 Tier 5 Tier 2
Dave
Not sure if this is what you wanted, but check out this file, it has formula to calculate Tier basing on your input including:
B2: Base
B3: Peak
B4: Number of Tier
C2:
To return Tier #
Please Login or Register to view this content.
D2:
The lowest of the 1st tier is always the basePlease Login or Register to view this content.
D3:
Simply the highest of the tier above plus 1. Drag down however long you want.Please Login or Register to view this content.
E2:
First highest = (Peak - Base) / Number of tiersPlease Login or Register to view this content.
E3:
= Base of the Tier, plus the different of Peak & Base, divided by # of Tiers, and minus 1. Drag down however long you want.Please Login or Register to view this content.
I simply use normal calculation to get these numbers.
Last edited by Lemice; 06-20-2016 at 02:56 AM.
(copy pasta from Ford)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
Regards,
Lem
Guys, I really appreciate all the help. Thank you.
Hi Lem,
As soon as I move the table, the outputs disappear, is there a way to stop this from happening?
Thanks,
Adam
@AdamLeeman
You have to be more specific to get a decent answer.
What did you do, cut and paste or copy and to what range?
It probably has to do with the row() in the formula, which create 1, 2, 3 etc.
If your new range is e.g. e25, e26 etc. the result of that formula will be 25, 26, 27 etc.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Removed by JT
Thanks, Oeldere.
I cut and pasted it, and moved it as well- all caused the data in cells to disappear. Is there a workaround by which I'm able to move the input cells ('number of tiers', 'base' and 'peak') independently without altering the output cells?
If you are just moving the 3 input cells, then change the references to these cells(B2,B3 and B4 in the supplied formula) to those of your new range.
So if you moved them to X2 to X4 then change B2 to X2 etc.
The Tier cells empty out once I move them.
you get better help showing us the excel file, without confidential information.
Not Sure why it won't attach to actual excel file... ! I've attached the image.
Rows 1-7 came from Lemice (the file is in this thread) and I want to apply them to the model in rows 12-26.
Cells B16, B17, and B18 will change.
Thanks,
Adam
I can't read the image, so I leave this to others, unless there comes an excel file.
Having said that, I would solved it like the way in #2 of FlameRetired
If I could e-mail this to you, I would.. unfortunately the attached function is not working.
@AdamLeemanMay I e-mail the excel file to you please?
Thanks,
Adam
emailadres adam
This is a public forum, so please use that, so please do not PM me.
You get better help using all members, instead of just 1 member.
Noted- thanks.
in #14 you showed the file, but not the formula, so I can't see if the formula you use is wrong.
That is why we can't help you without seeing the excel file.
I hope this works...
I think the formula in c2 produces the correct result.
What did you expect as result in c2?
I'm trying to move C2:E7 to A21:C26, but as soon as I move the cells, the data disappears. I don't know how to rework the formula.
There is an excel file that you can download in #3.
I'd also like to move B2 and B3 into B16 and B17, but I think this will not be an issue.
That is not your file, so there will not be your problem.There is an excel file that you can download in #3.
Try uploading your excel file.I'd also like to move B2 and B3 into B16 and B17, but I think this will not be an issue.
Did this work?
Yes that did work, but you don't have the moved data in cell A21:D26.
So we can't see what is going wrong.
So please post an excel file, with the errors in the file.
Please also add manualy the expected result in your file.
Following similar methodology of formulas found in cells C2:E7, I would like to format cells A21:C26 to output the same results as C2:E7
See the green cells in the attached file.
Thank you very much, Oeldere!!
You're welcome. We appreciate the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks