brennermac
New Member
- Joined
- Jul 7, 2020
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
Hello all,
I have been working to put together a spreadsheet for a job proposal. I work with an aeroponic company who supplies fresh produce to clients who will then give out the produce for free at their events. I need create a model where the variables are Ports/Plants, Price, expected attendance, plants per person, and how many towers we will use. She wants to do multiple events per month so she needs to be able to estimate her monthly cost. We can supplement through our greenhouse. Our aeroponic towers have 28 ports each, which means that we can get 28 plants off of each tower. One tower = $200/mo, two towers = $400/mo etc. If revenue is over $2,000, they will receive a 10% discount. If it is over $5000, they will receive a 20% discount. We want to be able to play with the numbers to present something feasible for her.
I have tried to put together formulas where you only need to type in expected attendance for each of the three events for example, and then price / # of towers / ports and plants / and plants per person would all change accordingly. The issue I am having, for example, with the number of towers per month, is that I am not sure how to simplify my formula and allow for any number of attendance at the events.
I currently have the formula:
=IFS(AND(D4>=28,D4<56),1,(AND(D4>=56,D4<84)),2,(AND(D4>=84,D4<112)),3,(AND(D4>=112,D4<140)),4,(AND(D4>=140,D4<168)),5)
where the number of towers is based of of the total expected attendance (D4) during the month. The numbers 28, 56, 84, 112, etc. represent the number of ports per tower. The value if true numbers, 1, 2, 3, etc. represent the number of towers. As of now, the formula only allows for a maximum attendance of 168. Is there a better way to model this or to simplify this formula that would allow for very large numbers of attendance, i.e. 3,000 attendees? Feel free to ask any questions and I will do my best to clarify. I will also attach my spreadsheet for any further explanation.
Thanks!
I have been working to put together a spreadsheet for a job proposal. I work with an aeroponic company who supplies fresh produce to clients who will then give out the produce for free at their events. I need create a model where the variables are Ports/Plants, Price, expected attendance, plants per person, and how many towers we will use. She wants to do multiple events per month so she needs to be able to estimate her monthly cost. We can supplement through our greenhouse. Our aeroponic towers have 28 ports each, which means that we can get 28 plants off of each tower. One tower = $200/mo, two towers = $400/mo etc. If revenue is over $2,000, they will receive a 10% discount. If it is over $5000, they will receive a 20% discount. We want to be able to play with the numbers to present something feasible for her.
I have tried to put together formulas where you only need to type in expected attendance for each of the three events for example, and then price / # of towers / ports and plants / and plants per person would all change accordingly. The issue I am having, for example, with the number of towers per month, is that I am not sure how to simplify my formula and allow for any number of attendance at the events.
I currently have the formula:
=IFS(AND(D4>=28,D4<56),1,(AND(D4>=56,D4<84)),2,(AND(D4>=84,D4<112)),3,(AND(D4>=112,D4<140)),4,(AND(D4>=140,D4<168)),5)
where the number of towers is based of of the total expected attendance (D4) during the month. The numbers 28, 56, 84, 112, etc. represent the number of ports per tower. The value if true numbers, 1, 2, 3, etc. represent the number of towers. As of now, the formula only allows for a maximum attendance of 168. Is there a better way to model this or to simplify this formula that would allow for very large numbers of attendance, i.e. 3,000 attendees? Feel free to ask any questions and I will do my best to clarify. I will also attach my spreadsheet for any further explanation.
Thanks!
Communal Group Pricing.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | The Communal Group | |||||||||||||
2 | ||||||||||||||
3 | ||||||||||||||
4 | Total Expected Attendence | 35 | Number of Events per Month | 4 | ||||||||||
5 | ||||||||||||||
6 | Towers | 1 | Event One Attendence | 35 | ||||||||||
7 | ||||||||||||||
8 | Price | 200 | Event Two Attendence | |||||||||||
9 | ||||||||||||||
10 | Ports / Plants | 28 | Event Three Attendence | |||||||||||
11 | ||||||||||||||
12 | Plants Per Person | 1 | Event Four Attendence | |||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D4 | D4 | =SUM(L6:L16) |
D6 | D6 | =IFS(AND(D4>=28,D4<56),1,(AND(D4>=56,D4<84)),2,(AND(D4>=84,D4<112)),3,(AND(D4>=112,D4<140)),4,(AND(D4>=140,D4<168)),5) |
H6 | H6 | =IF(L4>=1,"Event One Attendence","") |
D8 | D8 | =IFS(D6<10,D6*200,(AND(D6>=10,D6<25)),D6-(D6*0.1),D6>25,D6-(D6*0.2)) |
H8 | H8 | =IF(L4>=2,"Event Two Attendence","") |
D10 | D10 | =IFS(AND(D6>=1,D6<2),28,(AND(D6>=2,D6<3)),56,(AND(D6>=3,D6<4)),84,(AND(D6>=4,D6<5)),112,(AND(D6>=5,D6<6)),140) |
H10 | H10 | =IF(L4>=3,"Event Three Attendence","") |
D12 | D12 | =INT(D4/D10) |
H12 | H12 | =IF(L4>=4,"Event Four Attendence","") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
H12:J12 | Expression | =$L$4>=4 | text | NO |
H10:J10 | Expression | =$L$4>=3 | text | NO |
H8:J8 | Expression | =$L$4>=2 | text | NO |
H6:J6 | Expression | =$L$4>=1 | text | NO |
L12 | Expression | =$L$4>=4 | text | NO |
L10 | Expression | =$L$4>=3 | text | NO |
L8 | Expression | =$L$4>=2 | text | NO |
L6 | Expression | =$L$4>=1 | text | NO |