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" (halfbottle) 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:
16 DECANTS: J2<6, "1",
712 DECANTS: J2>6, "2",
1318 DECANTS: J2>12, "3",
1924 DECANTS: J2>18, "4",
2530 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" (halfbottle) 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 SCENTNAME,NAME,# DECANTS/HB,TOTAL DECANTS ONLY,TOTAL HBS,TOTAL DECANTS+HBS,HB LEFTOVERS,TOTAL BOTTLES NEEDED,LEFTOVER MLS,HBLEFTOVERS  DECANTS ONLY Test Scent,Test,1,4,5,19,15,6,11,11 1 hb hb hb hb 1 hb 1
docs.google.com
Here's a list I made that I think will help in the calculation:
16 DECANTS: J2<6, "1",
712 DECANTS: J2>6, "2",
1318 DECANTS: J2>12, "3",
1924 DECANTS: J2>18, "4",
2530 DECANTS: J2>24, "5"
Attachments

482.5 KB Views: 5

597.3 KB Views: 4
Last edited: