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 