# Clarifying / Simplifying Formula

#### brennermac

##### New Member
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
ABCDEFGHIJKL
1The Communal Group
2
3
4Total Expected Attendence35Number of Events per Month4
5
6Towers1Event One Attendence35
7
8Price200Event Two Attendence
9
10Ports / Plants28Event Three Attendence
11
12Plants Per Person1Event Four Attendence
Sheet1
Cell Formulas
RangeFormula
D4D4=SUM(L6:L16)
D6D6=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)
H6H6=IF(L4>=1,"Event One Attendence","")
D8D8=IFS(D6<10,D6*200,(AND(D6>=10,D6<25)),D6-(D6*0.1),D6>25,D6-(D6*0.2))
H8H8=IF(L4>=2,"Event Two Attendence","")
D10D10=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)
H10H10=IF(L4>=3,"Event Three Attendence","")
D12D12=INT(D4/D10)
H12H12=IF(L4>=4,"Event Four Attendence","")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H12:J12Expression=\$L\$4>=4textNO
H10:J10Expression=\$L\$4>=3textNO
H8:J8Expression=\$L\$4>=2textNO
H6:J6Expression=\$L\$4>=1textNO
L12Expression=\$L\$4>=4textNO
L10Expression=\$L\$4>=3textNO
L8Expression=\$L\$4>=2textNO
L6Expression=\$L\$4>=1textNO

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
=INT(D4/28)

#### brennermac

##### New Member
Hi & welcome to MrExcel.
=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

##### Well-known Member
Try =(CEILING(D4,28)/28)-1

#### Fluff

##### MrExcel MVP, Moderator
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

##### MrExcel MVP, Moderator
You could also replace D10 with
=D6*28

Replies
4
Views
179
Replies
20
Views
272
Replies
2
Views
123
Replies
2
Views
101
Replies
5
Views
438