avtrout
New Member
I run perfume decanting circles and I need a formula that calculates the following:
Every original bottle has 6mls. I purchase a bottle, and decant into 6 1ml vials and sell them. Although many people buy 1ml decants, some people buy "HB" (half-bottle) decants, which equal 3ml and come in the original bottle.
I need a calculation that tells me how many bottles to buy of a particular scent, taking into account that every HB has a leftover amount of 3mls. I want to use the leftover accumulated 3mls from each HB to fill my 1ml buyers orders. If you buy a HB, you get the original bottle. So every HB is essentially a whole bottle purchase (on my end) with 3mls leftover that I sell in 1ml increments to 3 other people. (I hope this isn't confusing).
I'm including a link to the spreadsheet. I'm trying to figure out the value of H2. Here's what I have:
=SUM(COUNTIF(C2:C30,{"hb"})*{1},(IFS(J2>=7, "1", J2>=7,"2",J2<=6, "1")))
I also need the final answer to ROUNDUP. I can't purchase 1.3 bottles or something like that. So if a particular scent has only 2 1ml purchases, H2 needs to return the value of "1". Maybe I don't need to ROUNDUP. I'm not sure.
Here's a list I made that I think will help in the calculation:
1-6 DECANTS: J2<6, "1",
7-12 DECANTS: J2>6, "2",
13-18 DECANTS: J2>12, "3",
19-24 DECANTS: J2>18, "4",
25-30 DECANTS: J2>24, "5"
Every original bottle has 6mls. I purchase a bottle, and decant into 6 1ml vials and sell them. Although many people buy 1ml decants, some people buy "HB" (half-bottle) decants, which equal 3ml and come in the original bottle.
I need a calculation that tells me how many bottles to buy of a particular scent, taking into account that every HB has a leftover amount of 3mls. I want to use the leftover accumulated 3mls from each HB to fill my 1ml buyers orders. If you buy a HB, you get the original bottle. So every HB is essentially a whole bottle purchase (on my end) with 3mls leftover that I sell in 1ml increments to 3 other people. (I hope this isn't confusing).
I'm including a link to the spreadsheet. I'm trying to figure out the value of H2. Here's what I have:
=SUM(COUNTIF(C2:C30,{"hb"})*{1},(IFS(J2>=7, "1", J2>=7,"2",J2<=6, "1")))
I also need the final answer to ROUNDUP. I can't purchase 1.3 bottles or something like that. So if a particular scent has only 2 1ml purchases, H2 needs to return the value of "1". Maybe I don't need to ROUNDUP. I'm not sure.
Circle FORMULA Workhseet
Sheet1 SCENT NAME,DECANTS FB,DECANTS FORUM,DECANT TOTAL,ADD-ON BOTTLES,TOTAL BOTTLES,LEFTOVER MLS,HBLEFTOVERS - DECANTS ONLY,ADD-ON BOTTLE NAME,LAB ORDERS SAMPLE,3,5,8,1,3,SALLY SAMPLE TOTALS,95,43,138,5,49,200584 9/25 Etienne De Boray,3,3,1 #2 Pencil,1,1,2,1,2,Katie L. Baer,1 2020 Aesthetic,3,3...
docs.google.com
Here's a list I made that I think will help in the calculation:
1-6 DECANTS: J2<6, "1",
7-12 DECANTS: J2>6, "2",
13-18 DECANTS: J2>12, "3",
19-24 DECANTS: J2>18, "4",
25-30 DECANTS: J2>24, "5"
Attachments
Last edited: