I play a game that gives a production bonus for having certain percentages more than the required "culture". The picture here shows the different percentages available, the requirement for the current level, etc. What I would like to have is a set of formulas that will calculate the "available culture" needed for the other levels based on the known requirements. For instance from the picture, I would want to be able to calculate the 100%, 125%, and 170%. The picture already give the amounts needed for the 160% being "Available Culture + Neighborly Help Bonus + Required for next level. So if I wanted to have enough Available Culture for the 160% in this case I would need a total of 260K. However, what I really want is to know what the available could drop to and still be at 125%. The Neighborly Help Bonus varies from day to day and sometimes hour to hour so I like to ensure I have at least a 125% base. I hope this makes sense.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Percentage Calculation formula
- Thread starter BGDunbar
- Start date
Eric W
MrExcel MVP
- Joined
- Aug 18, 2015
- Messages
- 12,980
It's kind of an unclear question. From what I gather, you want to hold the Neighborly Help Bonus and the Needed for next level values constant, and vary the Available Culture value to see what it would take to hit the other levels. Something like this maybe:
Put the values in B3:B5, and put an X over the current level, then the formulas in D3:H3 should give you the values you want. HOWEVER, this will be a very rough estimate! Since the levels jump between 100, 125, 150, 160, and 170, then the in between values are ignored. If you need 100000 for example to hit the 100% level exactly, then you'd need 125000 to hit the 125% level. But if you have 124999, it'll still show up as being in the 100% level, but the formulas would show you needing several thousand points away, when in reality you're only 1 point away. Since I don't know the exact algorithm they use, these numbers are guesses, but should illustrate the point.
In any event, perhaps this estimate might be good enough for you. Good luck!
| Book1 | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | Current level: | X | ||||||||
| 2 | 100% | 125% | 150% | 160% | 170% | |||||
| 3 | Available Culture | 126419 | 39752.33333 | 83085.66667 | 143752.3333 | 161085.6667 | ||||
| 4 | Neighborly Help Bonus | 82940 | ||||||||
| 5 | Needed for next level | 50641 | ||||||||
Sheet4 | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D3:H3 | D3 | =IF(D1="x","",D2/INDEX($D$2:$H$2,MATCH("X",$D$1:$H$1,0))*SUM($B$3:$B$5)-$B$4-$B$5) |
Put the values in B3:B5, and put an X over the current level, then the formulas in D3:H3 should give you the values you want. HOWEVER, this will be a very rough estimate! Since the levels jump between 100, 125, 150, 160, and 170, then the in between values are ignored. If you need 100000 for example to hit the 100% level exactly, then you'd need 125000 to hit the 125% level. But if you have 124999, it'll still show up as being in the 100% level, but the formulas would show you needing several thousand points away, when in reality you're only 1 point away. Since I don't know the exact algorithm they use, these numbers are guesses, but should illustrate the point.
In any event, perhaps this estimate might be good enough for you. Good luck!
Upvote
0
Similar threads
