Clarifying / Simplifying Formula

[brennermac]

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!

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

 

[Fluff]

Hi & welcome to MrExcel.
How about
=INT(D4/28)

 

[brennermac]

Hi & welcome to MrExcel.
How about
=INT(D4/28)

Hey, I was using that formula to round down the number of plants per person that would be available at an event.

 

[arthurbr]

Try =(CEILING(D4,28)/28)-1

 

[Fluff]

Hey, I was using that formula to round down the number of plants per person that would be available at an event.

And you can use it in D6 as well

 

[Fluff]

You could also replace D10 with
=D6*28