Discussion Re: VERY NEW to EXCEL in Excel
https://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2305119#M98021
One thing, after looking at the formula it's actually 8 hours work day not 8.5, its the time when i input the numbers ,that throw me off, there 2 hour blocks.<BR />Thu, 29 Apr 2021 03:28:40 GMTDale31582021-04-29T03:28:40ZVERY NEW to EXCEL
https://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2300252#M97882
<P>How do i create a formula to Average blocks of time?</P><P>I input the units for each block of time and i can get the Average after 8 hours,</P><P>but i need a running average of each block of time though out the night.</P><P>So my AVG. UPH (units per hour)column, when asked, for example the first two column of time equal </P><P>50, so my AVG. UPH would be 50/4.5 hours = 11.11.</P><P>Hope i explained this right.</P><P>Thanks for any help</P><P>Dale3158</P><TABLE width="1320"><TBODY><TR><TD width="220">3:30-6:00pm 2.5 Hrs.</TD><TD width="220">6:00-8:00pm 2 Hrs.</TD><TD width="220">8:00-10:00pm 2 Hrs.</TD><TD width="220">10:00-12:00am 2 Hrs</TD><TD width="220">TOTAL</TD><TD width="220">AVG - UPH</TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>=SUM(C2:F2)</TD><TD>=SUM(G2)/8</TD></TR><TR><TD>25</TD><TD>25</TD><TD>25</TD><TD>25</TD><TD>=SUM(C3:F3)</TD><TD>=SUM(G3)/8</TD></TR></TBODY></TABLE>Wed, 28 Apr 2021 01:57:01 GMThttps://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2300252#M97882Dale31582021-04-28T01:57:01ZRe: VERY NEW to EXCEL
https://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2300590#M97892
<P><LI-USER uid="1038993"></LI-USER> </P><P> </P><P>I attached an example workbook of what I think you're trying to do. I split the start time, end time, and hours into separate cells, added a formula to compute the hours, and added a formula to compute the UPH. </P><P> </P><P>You could hardcode the hours instead of using a formula, but I would at least put the hours in a separate cell. Also, note that I used a custom number format to add the "Hrs" label - it is not actually part of the cell contents as that would cause an error in the UPH formula.</P><P> </P>Wed, 28 Apr 2021 05:31:01 GMThttps://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2300590#M97892JMB172021-04-28T05:31:01ZRe: VERY NEW to EXCEL
https://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2304807#M98015
<P><LI-USER uid="675152"></LI-USER> </P><P>Thank you very much.</P><P>Is there a way to copy and paste the formula's into my spreadsheet,</P><P>Here is a copy of my spreadsheet, I use everyday, columns A,B and I will change from time to time, as my group changes.</P><P>Maybe I should have showed the spreadsheet in the first post.</P><P>My boss really has a thing for UPH.</P><TABLE width="1504"><TBODY><TR><TD width="145"> </TD><TD width="217"> </TD><TD width="145">3:30 PM</TD><TD width="145">6:00 PM</TD><TD width="145">8:00 PM</TD><TD width="145">10:00 PM</TD><TD width="145"> </TD><TD width="145"> </TD><TD width="272"> </TD></TR><TR><TD> </TD><TD> </TD><TD>6:00 PM</TD><TD>8:00 PM</TD><TD>10:00 PM</TD><TD>12:00 AM</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD>2.50 Hrs</TD><TD>2.00 Hrs</TD><TD>2.00 Hrs</TD><TD>2.00 Hrs</TD><TD>Total</TD><TD>UPH</TD><TD> </TD></TR><TR><TD>ID NUMBER</TD><TD>NAME</TD><TD>10</TD><TD>43</TD><TD>25</TD><TD>27</TD><TD>105</TD><TD>12.35</TD><TD>COMMENTS</TD></TR><TR><TD>8836</TD><TD>TORY YOUNG</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD> </TD><TD> </TD></TR><TR><TD>8740</TD><TD>ANTHONY SAMEC</TD><TD>41</TD><TD>32</TD><TD>24</TD><TD>26</TD><TD>123</TD><TD> </TD><TD> </TD></TR><TR><TD>8138</TD><TD>SANDRA ORR</TD><TD>57</TD><TD>23</TD><TD>51</TD><TD>98</TD><TD>229</TD><TD> </TD><TD> </TD></TR><TR><TD>8978</TD><TD>CLAUDREZ THOMPSON</TD><TD>43</TD><TD>44</TD><TD>25</TD><TD>34</TD><TD>146</TD><TD> </TD><TD> </TD></TR><TR><TD>8945</TD><TD>KIRK WILSON</TD><TD>46</TD><TD>40</TD><TD>36</TD><TD>44</TD><TD>166</TD><TD> </TD><TD> </TD></TR><TR><TD>8947</TD><TD>SCOTT BRALLIER</TD><TD>43</TD><TD>46</TD><TD>49</TD><TD>51</TD><TD>189</TD><TD> </TD><TD> </TD></TR><TR><TD>8980</TD><TD>TAYLOR WALTON</TD><TD>28</TD><TD>21</TD><TD>32</TD><TD>25</TD><TD>106</TD><TD> </TD><TD> </TD></TR><TR><TD>8930</TD><TD>STEVEN GUEVARA</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD> </TD><TD> </TD></TR><TR><TD>8931</TD><TD>JEVELEISKA AVELLANET</TD><TD>19</TD><TD>29</TD><TD>24</TD><TD>27</TD><TD>99</TD><TD> </TD><TD>start 4:30 </TD></TR><TR><TD>8987</TD><TD>BROOKE CROTHERS</TD><TD>40</TD><TD>52</TD><TD>31</TD><TD>40</TD><TD>163</TD><TD> </TD><TD> </TD></TR><TR><TD>8988</TD><TD>CANDACE DELLAROVA</TD><TD>9</TD><TD>25</TD><TD>23</TD><TD>27</TD><TD>84</TD><TD> </TD><TD> TEO UNTIL 5:00 THEN FO RECV. </TD></TR><TR><TD>N/A</TD><TD>CLIFFORD WILLIAMS</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD> </TD><TD>TRASH AND CARDBOARD.</TD></TR></TBODY></TABLE>Wed, 28 Apr 2021 23:36:01 GMThttps://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2304807#M98015Dale31582021-04-28T23:36:01ZRe: VERY NEW to EXCEL
https://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2305119#M98021
One thing, after looking at the formula it's actually 8 hours work day not 8.5, its the time when i input the numbers ,that throw me off, there 2 hour blocks.<BR />Thu, 29 Apr 2021 03:28:40 GMThttps://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2305119#M98021Dale31582021-04-29T03:28:40ZRe: VERY NEW to EXCEL
https://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2307863#M98104
<P><LI-USER uid="1038993"></LI-USER> </P><P> </P><P>You just have to change the cell references according to where the data is located in your spreadsheet. Your screenshot does not include row/column labels, but assuming the upper left corner is cell A1, then the formula for UPH in cell H5 would be:</P><P> </P><P>=G5/SUMIF(C5:F5,">0",C$3:F$3)</P><P> </P><P>For the hours in Row 3, you may just want to key in the number of hours instead of computing them in order to exclude the half hour that I assume is lunch.</P><P> </P>Thu, 29 Apr 2021 17:15:32 GMThttps://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2307863#M98104JMB172021-04-29T17:15:32ZRe: VERY NEW to EXCEL
https://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2308818#M98137
Thank you very much, your help is greatly appreciated.<BR />I will let you know how it turns out.<BR />DaleThu, 29 Apr 2021 22:02:41 GMThttps://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2308818#M98137Dale31582021-04-29T22:02:41ZRe: VERY NEW to EXCEL
https://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2319950#M98603
JMB17<BR />Have a question, can I use a IF statement to change the color of a cell?<BR />So, say my Expectation's for my UPH was 25 so that cell would turn green and if lower than 25 turn red.<BR />I have been reading about the IF statement but not sure how to use it to change the color of a cell.<BR />Thanks<BR />Dale<BR />Tue, 04 May 2021 02:28:54 GMThttps://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2319950#M98603Dale31582021-05-04T02:28:54ZRe: VERY NEW to EXCEL
https://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2320396#M98617
<P><LI-USER uid="1038993"></LI-USER> </P><P> </P><P>Look at using conditional formatting. Click on the home tab, conditional formatting split button in the styles group, and select new rule. This will bring up a dialog box. Then, select "format only cells that contain and fill in your criteria and select your formatting:</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="JMB17_0-1620106152442.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/277755iF1D1F99DEC18BABF/image-size/medium?v=v2&px=400" role="button" title="JMB17_0-1620106152442.png" alt="JMB17_0-1620106152442.png" /></span></P><P> </P><P>There is also an option to "use a formula to determine which cells to format" for more complex/custom criteria - that may be to what your reference material is referring.</P>Tue, 04 May 2021 05:34:10 GMThttps://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2320396#M98617JMB172021-05-04T05:34:10ZRe: VERY NEW to EXCEL
https://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2333131#M99013
Thank You, JMB17<BR />I will try it.<BR />Dale3158Thu, 06 May 2021 21:14:02 GMThttps://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2333131#M99013Dale31582021-05-06T21:14:02ZRe: VERY NEW to EXCEL
https://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2353483#M99792
<P><LI-USER uid="675152"></LI-USER> </P><P>I tried conditional formatting, it worked ok, my problem is, when I have to move someone to a different job, where the UPH change, it didn't seem to work. </P><P>Column I is where I'm having problems.</P><P>I included my spreadsheet.</P><P>Any suggestions</P><P>Thanks</P><P>Dale</P><P> </P>Fri, 14 May 2021 02:34:40 GMThttps://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2353483#M99792Dale31582021-05-14T02:34:40ZRe: VERY NEW to EXCEL
https://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2356630#M99898
<P><LI-USER uid="1038993"></LI-USER> </P><P> </P><P>I assume the conditional formatting should be applied to Column H? I misunderstood earlier that the expectation was not a fixed amount across the board, but was in a separate column. So, a formula would be needed for the conditional format.</P><P> </P><P>First, the values in expectations must be numbers. If you key "23/Hr" into a cell, then excel treats it as text and any text value is considered greater than every numeric value in a conditional statement. So, I input only the numeric values in Column I, but applied a custom number format to display it as "23/Hr" - the "/Hr" part does not exist in the cell data (formula bar), but only on screen as fancy formatting.</P><P> </P><P>Then, I selected H5:H16, clicked conditional formatting, and entered the formula</P><P>=H5>=I5</P><P>and selected a green fill color (you enter the formula as it applies to the cell in the top left of your selected range, and excel will apply it to the rest of the range).</P><P> </P><P>Then, I added another conditional format using the formula</P><P>=H5<I5</P><P>and selected a red fill color.</P><P> </P><P>Bear in mind that if you add to your data, you may need to expand your conditional formatting by going into the conditional format dialog and changing the "applies to" range as needed.</P><P> </P><P> </P>Sat, 15 May 2021 02:04:46 GMThttps://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2356630#M99898JMB172021-05-15T02:04:46ZRe: VERY NEW to EXCEL
https://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2370577#M100537
<P><LI-USER uid="675152"></LI-USER> </P><P>Thank you very much, really appreciate the help.</P><P>There is so much to learn in Excel.</P><P>Dale</P>Thu, 20 May 2021 01:49:31 GMThttps://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2370577#M100537Dale31582021-05-20T01:49:31ZRe: VERY NEW to EXCEL
https://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2370578#M100538
Indeed. You're quite welcome.Thu, 20 May 2021 01:52:21 GMThttps://techcommunity.microsoft.com/t5/excel/very-new-to-excel/m-p/2370578#M100538JMB172021-05-20T01:52:21Z