Clarifying / Simplifying Formula

brennermac

New Member
Joined
Jul 7, 2020
Messages
3
Office Version
  1. 365
Platform
  1. 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!

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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,973
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
=INT(D4/28)
 

brennermac

New Member
Joined
Jul 7, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,973
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
47,973
Office Version
  1. 365
Platform
  1. Windows
You could also replace D10 with
=D6*28
 

Watch MrExcel Video

Forum statistics

Threads
1,113,775
Messages
5,544,152
Members
410,595
Latest member
Tatum2020
Top