Need help with a tricky formula- optimizing hourly wages

[Lisa Mason]

Hi there, I am really stumped with a formula I am trying to write in Excel and I thought someone here would probably be able to steer me in the right direction.

Here's the situation: I buy mixed lots of items in bulk and then re-sell the items individually for a profit. Every item takes me 30 minutes to prep and sell, whether it's worth $1 or $100. So, I would like to write a formula in Excel that will maximize my hourly earnings for each "lot" of items I buy. That way I can just fix up and sell certain items (above a certain threshold) and the ones that are not worth enough to increase my hourly wages I will not invest time into fixing them up and selling them.

I can input the cost (what I paid) for the lot. I will also input the projected value of each item.

Here is an example data set:

Column A Column B
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=64>Item 1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64 align=right x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>50</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 7</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 8</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 9</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 10</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 11</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 12</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 13</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 14</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 15</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 16</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 17</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 18</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>225</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 19</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 20</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 21</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 22</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>25</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 23</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 24</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 25</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 26</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 27</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 28</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 29</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 30</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 31</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>15</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 32</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>15</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 33</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Item 34</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right x:num>5</TD></TR></TBODY></TABLE>


Cost: $282


I can calculate my hourly wage from fixing all of these items up (should be $15/hr in profits). But what I want to know is, could I make more per hour by throwing out some of the lower value items and only fixing up the higher value ones. Thanks for the help; I hope my question is clear!

 

[Michael M]

Hi Lisa and Welcome to the Board.
I think you need to provide a little more information.
What is column "B" ? Cost of Item, time to fix, project price, cost to fix ???
Try providing us with a smaller data set with a worked example, that will give us a better idea of your need.

 

[Lisa Mason]

Michael,

Thank you for the response and sorry for being too vague. Here is a small dataset/example:

<table style="width: 151px; height: 265px;" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" span="2" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" width="64">Item</td> <td style="width:48pt" width="64">Value ($)</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Item 1</td> <td align="right">5</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Item 2</td> <td align="right">7</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Item 3</td> <td align="right">50</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Item 4</td> <td align="right">7</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Item 5</td> <td align="right">6</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Item 6</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Item 7</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Item 8</td> <td align="right">12</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Item 9</td> <td align="right">10</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Item 10</td> <td align="right">4</td> </tr> </tbody></table>


The cost of this lot is $42.00 (what I paid for it). The value (what I will sell each piece for) of all the pieces is $117.00 (sum of the value column). The profit is $75.00 (value - cost). Since it takes me 30 minutes to prep and sell each piece, that is 5 hours of work to earn $75, or $15.00/hr.

If I wanted to increase my hourly wage I could remove items 1 and 10. That would bring my hourly wage up to $16.50/hr (the new profit is $66.00 (new value - cost)). Since I would only be prepping 8 items, it would only take me 4 hours to earn $66.00, or $16.50/hr.

So, as you can see, it is time consuming for every lot to monkey around with which items I should invest my time in prepping and selling in order to maximize my hourly profits. I would like to devise a formula that will give me a threshold with which to decide. e.g. Any item worth less than $6.00 is not worth investing time into selling.

Does that make more sense?

 

[Michael M]

I would have thought you could create a small table and adjusted the time spent to see the adjusted rate. If you change the adjusted Time cell in the table it will change the hourly rate. You could also use Tools / Solver on your table

I'm sure there are other posters out there will come up with a stat formula to do it really simply.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">13</td><td style="background-color: #CCFFCC;;">Total Cost</td><td style="text-align: right;background-color: #CCFFCC;;"> $ 42.0 </td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Item</td><td style=";"> Value ($) </td><td style=";">Prep</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Item 1</td><td style="text-align: right;;"> $ 5.0 </td><td style="text-align: right;;"> 7.50 </td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Item 2</td><td style="text-align: right;;"> $ 7.0 </td><td style="text-align: right;;"> 7.50 </td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">Item 3</td><td style="text-align: right;;"> $ 50.0 </td><td style="text-align: right;;"> 7.50 </td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">Item 4</td><td style="text-align: right;;"> $ 7.0 </td><td style="text-align: right;;"> 7.50 </td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">Item 5</td><td style="text-align: right;;"> $ 6.0 </td><td style="text-align: right;;"> 7.50 </td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">Item 6</td><td style="text-align: right;;"> $ 8.0 </td><td style="text-align: right;;"> 7.50 </td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">Item 7</td><td style="text-align: right;;"> $ 8.0 </td><td style="text-align: right;;"> 7.50 </td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">Item 8</td><td style="text-align: right;;"> $ 12.0 </td><td style="text-align: right;;"> 7.50 </td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">Item 9</td><td style="text-align: right;;"> $ 10.0 </td><td style="text-align: right;;"> 7.50 </td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";">Item 10</td><td style="text-align: right;;"> $ 4.0 </td><td style="text-align: right;;"> 7.50 </td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="font-weight: bold;background-color: #FFFF99;;">Normal Time Reqd</td><td style="font-weight: bold;text-align: center;background-color: #FFFF99;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="font-weight: bold;background-color: #FFFF99;;">Total Value </td><td style="font-weight: bold;text-align: right;background-color: #FFFF99;;"> $ 117.0 </td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="background-color: #FFFF99;;">Adjusted Time Reqd</td><td style="font-weight: bold;text-align: center;background-color: #CCFFFF;;">5</td><td style="text-align: right;background-color: #FFFF99;;"> 15.00 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K16</th><td style="text-align:left">=(<font color="Blue">$J$28-$J$13</font>)/COUNT(<font color="Blue">$J$16:$J$25</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K17</th><td style="text-align:left">=(<font color="Blue">$J$28-$J$13</font>)/COUNT(<font color="Blue">$J$16:$J$25</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K18</th><td style="text-align:left">=(<font color="Blue">$J$28-$J$13</font>)/COUNT(<font color="Blue">$J$16:$J$25</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K19</th><td style="text-align:left">=(<font color="Blue">$J$28-$J$13</font>)/COUNT(<font color="Blue">$J$16:$J$25</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K20</th><td style="text-align:left">=(<font color="Blue">$J$28-$J$13</font>)/COUNT(<font color="Blue">$J$16:$J$25</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K21</th><td style="text-align:left">=(<font color="Blue">$J$28-$J$13</font>)/COUNT(<font color="Blue">$J$16:$J$25</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K22</th><td style="text-align:left">=(<font color="Blue">$J$28-$J$13</font>)/COUNT(<font color="Blue">$J$16:$J$25</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K23</th><td style="text-align:left">=(<font color="Blue">$J$28-$J$13</font>)/COUNT(<font color="Blue">$J$16:$J$25</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K24</th><td style="text-align:left">=(<font color="Blue">$J$28-$J$13</font>)/COUNT(<font color="Blue">$J$16:$J$25</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K25</th><td style="text-align:left">=(<font color="Blue">$J$28-$J$13</font>)/COUNT(<font color="Blue">$J$16:$J$25</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J27</th><td style="text-align:left">=COUNT(<font color="Blue">J16:J25</font>)/2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K29</th><td style="text-align:left">=SUM(<font color="Blue">K16:K25</font>)/J29</td></tr></tbody></table></td></tr></table><br />

 

[TwistShout]

I wrote a very simple VBA code to automatically do this. Just start a column A2 going down of item names and at B2 going down of item values. It will output the average per hour, going from D2 and down(for the value per hour of doing that item and everything above it). It will also "highlight" the values if they dip below $15 per hour. It will also keep track of the number of minutes so far in the F2 ( and down) column.

paste this code into a macro and attach the macro to a button on the page
(this is very easy to do if you aren't familiar, but i do not have time to write up how. google or this forum should have an easy answer guide)

'declares variables
Dim i As Single, myrange As Single, cumave As Single, y As Single

Worksheets("Sheet1").Range("A1").Value = "Items"
Worksheets("Sheet1").Range("B1").Value = "Values"
Worksheets("Sheet1").Range("D1").Value = "Average"
Worksheets("Sheet1").Range("F1").Value = "Time"

'sorts the values and item names
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Range("B2"), Range("B2").End(xlDown)), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range(Range("A1"), Range("B1").End(xlDown))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

'counts how many items in data set + initialize y
myrange = Application.WorksheetFunction.CountA(Range(Range("B2"), Range("B2").End(xlDown)))

y = 0

'for loop through the value list
For i = 0 To myrange - 1

'computes the average per hour + per hour text
cumave = Application.WorksheetFunction.Average(Worksheets("Sheet1").Range(Range("B2"), Range("B2").Offset(i, 0)))
Worksheets("Sheet1").Range("B2").Offset(i, 2).Value = cumave * 2
Worksheets("Sheet1").Range("B2").Offset(i, 3).Value = "per hour"
Worksheets("Sheet1").Range("B2").Offset(i, 4).Value = 30 + y
Worksheets("Sheet1").Range("B2").Offset(i, 5).Value = "minutes"
y = y + 30
'colors out cell if value less than $15
If Not Worksheets("Sheet1").Range("B2").Offset(i, 2).Value > 15 Then
Worksheets("Sheet1").Range("B2").Offset(i, 2).Interior.ColorIndex = 44
End If

'changes to $ money format
Worksheets("Sheet1").Range("B2").Offset(i, 2).Select
Selection.Style = "Currency"

Next i

 

[TwistShout]

had a small update (it wouldnt let me edit)

'declares variables
Dim i As Single, myrange As Single, cumave As Single, y As Single

Worksheets("Sheet1").Range("A1").Value = "Items"
Worksheets("Sheet1").Range("B1").Value = "Values"
Worksheets("Sheet1").Range("D1").Value = "Average"
Worksheets("Sheet1").Range("F1").Value = "Time"


'sorts the values and item names
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Range("B2"), Range("B2").End(xlDown)), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range(Range("A1"), Range("B1").End(xlDown))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

'counts how many items in data set + initialize y
myrange = Application.WorksheetFunction.CountA(Range(Range("B2"), Range("B2").End(xlDown)))

y = 0

'for loop through the value list
For i = 0 To myrange - 1

'computes the average per hour + per hour text
cumave = Application.WorksheetFunction.Average(Worksheets("Sheet1").Range(Range("B2"), Range("B2").Offset(i, 0)))
Worksheets("Sheet1").Range("B2").Offset(i, 2).Value = cumave * 2
Worksheets("Sheet1").Range("B2").Offset(i, 3).Value = "per hour"
Worksheets("Sheet1").Range("B2").Offset(i, 4).Value = 30 + y
Worksheets("Sheet1").Range("B2").Offset(i, 5).Value = "minutes"
y = y + 30
'colors out cell if value less than $15
If Not Worksheets("Sheet1").Range("B2").Offset(i, 2).Value > 15 Then
Worksheets("Sheet1").Range("B2").Offset(i, 2).Interior.ColorIndex = 3
End If

'changes to $ money format
Worksheets("Sheet1").Range("B2").Offset(i, 2).Select
Selection.Style = "Currency"

Next i

Worksheets("Sheet1").Range("B2").Offset(myrange + 1, 0).Value = Application.WorksheetFunction.Sum(Range(Range("B2"), Range("B2").End(xlDown)))
Worksheets("Sheet1").Range("B2").Offset(myrange + 1, 0).Interior.ColorIndex = 4
Worksheets("Sheet1").Range("B2").Offset(myrange + 1, 0).Style = "Currency"
Worksheets("Sheet1").Range("B2").Offset(myrange + 1, -1).Value = "Sum"

 

[Lisa Mason]

TwistShout-

Thank you for taking the time to write this code! I did put it into a macro and got this error when I tried to run it: "compile error invalid outside procedure"

I tried googling the error and it said something about needing a "sub line", but I'm not sure what that means exactly.

One more thing, is there a way to incorporate the cost of the item? I would like the $/hour to be not solely the dollars per hour I earn to sell the item, but the "profit dollars" per hour. In essence, I want the dollars/hour earned to be calculated as ("value" - "cost") / time. I hope that make sense. Because, if I maximize the $/hr without taking into account what I paid for the item, then I might make alot per hour, but not even make back my money on the item. Ugh, I hope that makes sense...

 

[R Pelle]

Add the following

Sub MyProfit()
TwistShout's code
End Sub

Then Run macro

 

[Lisa Mason]

Thanks, that was easy

Now when I run the code it gives me the error: "Run time error '438' Object doesn't support this proerty or method".

And it points to this part of the code:

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Range("B2"), Range("B2").End(xlDown)), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal


Any ideas there?

 

[R Pelle]

Twistshout's macro works based on your example in post #3.
So Items in Column A and Value in Column B.
Did you rename your sheet? If you did you need to adjust the macro to look at your name instead of Sheet1