Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare & sum previous month production by workday.
I need a formula to compare the pound production of the current month, by
workdays, with the previous month. I have turned sumif and countif every way I can and still can't get it to give me right total pounds. To complicate the problem, the spreadsheet has the days across the rows with a column for hours next to each day and then a week to date total at the end of each week. I have Excel 2007. Ex. Monday 2/3 Tuesday 2/4 Wednesday 2/5 Thursday 2/6 Friday 2/7 WeektoDate Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds 11111 6.5 22222 7.0 33333 7.0 44444 7.0 55555 6.5 166665 There is then a column with the Month to date total. I have added a column after that and want it to show the matching workdays amounts. Meaning, if I have only 13 days of production so far this month I want the total of the first 13 days of production in the previous month. Both months are in the same workbook. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare & sum previous month production by workday.
Try this macro, I have assumed you have the values in ColA. Please change as
required. If you are new to macros; set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module and paste the below code. Save. Get back to Workbook. Tools|Macro|Run Macro Sub InsertBlanksAfter100() Dim intTotal As Integer Dim lngRow As Long Dim lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = lngLastRow To 1 Step -1 If intTotal = 100 Then ActiveSheet.Rows(lngRow + 1).Insert intTotal = 0 End If intTotal = intTotal + Range("A" & lngRow) Next End Sub If this post helps click Yes --------------- Jacob Skaria "Cindy" wrote: I need a formula to compare the pound production of the current month, by workdays, with the previous month. I have turned sumif and countif every way I can and still can't get it to give me right total pounds. To complicate the problem, the spreadsheet has the days across the rows with a column for hours next to each day and then a week to date total at the end of each week. I have Excel 2007. Ex. Monday 2/3 Tuesday 2/4 Wednesday 2/5 Thursday 2/6 Friday 2/7 WeektoDate Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds 11111 6.5 22222 7.0 33333 7.0 44444 7.0 55555 6.5 166665 There is then a column with the Month to date total. I have added a column after that and want it to show the matching workdays amounts. Meaning, if I have only 13 days of production so far this month I want the total of the first 13 days of production in the previous month. Both months are in the same workbook. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare & sum previous month production by workday.
Sorry the below is a wrong post
-- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try this macro, I have assumed you have the values in ColA. Please change as required. If you are new to macros; set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module and paste the below code. Save. Get back to Workbook. Tools|Macro|Run Macro Sub InsertBlanksAfter100() Dim intTotal As Integer Dim lngRow As Long Dim lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = lngLastRow To 1 Step -1 If intTotal = 100 Then ActiveSheet.Rows(lngRow + 1).Insert intTotal = 0 End If intTotal = intTotal + Range("A" & lngRow) Next End Sub If this post helps click Yes --------------- Jacob Skaria "Cindy" wrote: I need a formula to compare the pound production of the current month, by workdays, with the previous month. I have turned sumif and countif every way I can and still can't get it to give me right total pounds. To complicate the problem, the spreadsheet has the days across the rows with a column for hours next to each day and then a week to date total at the end of each week. I have Excel 2007. Ex. Monday 2/3 Tuesday 2/4 Wednesday 2/5 Thursday 2/6 Friday 2/7 WeektoDate Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds 11111 6.5 22222 7.0 33333 7.0 44444 7.0 55555 6.5 166665 There is then a column with the Month to date total. I have added a column after that and want it to show the matching workdays amounts. Meaning, if I have only 13 days of production so far this month I want the total of the first 13 days of production in the previous month. Both months are in the same workbook. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare & sum previous month production by workday.
Hi Cindy
Suppose you have the Sheets named Jan,Feb,Mar,Apr and you are currently working on Apr. I assume you insert two columns every day for Pounds and Hours just before the WeektoDate column/ If my assumption is correct you can try the below I assume we have 5 days represented from Col A to Col K. For the pound total in the current sheet use the below formula in cell L3. =SUMPRODUCT(--($A2:K2="Pounds"),--($A3:K3)) In M3 where you need to get the details from the previous sheet ie(Mar) use the below formula =SUMPRODUCT(--(Mar!$A2:K2="Pounds"),--(Mar!$A3:K3)) For having this formula rolling; leave 1 column blank and always insert a column from col J. If this post helps click Yes --------------- Jacob Skaria "Cindy" wrote: I need a formula to compare the pound production of the current month, by workdays, with the previous month. I have turned sumif and countif every way I can and still can't get it to give me right total pounds. To complicate the problem, the spreadsheet has the days across the rows with a column for hours next to each day and then a week to date total at the end of each week. I have Excel 2007. Ex. Monday 2/3 Tuesday 2/4 Wednesday 2/5 Thursday 2/6 Friday 2/7 WeektoDate Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds 11111 6.5 22222 7.0 33333 7.0 44444 7.0 55555 6.5 166665 There is then a column with the Month to date total. I have added a column after that and want it to show the matching workdays amounts. Meaning, if I have only 13 days of production so far this month I want the total of the first 13 days of production in the previous month. Both months are in the same workbook. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare & sum previous month production by workday.
Hi Cindy
I'm not saying it is impossible to do what you want, but it would be very complicated. Unfortunately, you have confused the storage of data, with reporting. They would be best on separate sheets. All of your data for the year (or longer) should be held on one sheet in the format Date Pounds Hours with a row for each day going down the sheet. Then you should have a Report sheet which pulls across the data that you want to see, relative to an Start date that you give at the top of the sheet. It would be quite easy to convert your existing data to the new layout with a small amount of VBA. If you are able to send me your workbook, I would be happy to convert it for you and return. To mail direct, send to roger at technology4u dot co dot uk Change the at and dots to make valid email address -- Regards Roger Govier "Cindy" wrote in message ... I need a formula to compare the pound production of the current month, by workdays, with the previous month. I have turned sumif and countif every way I can and still can't get it to give me right total pounds. To complicate the problem, the spreadsheet has the days across the rows with a column for hours next to each day and then a week to date total at the end of each week. I have Excel 2007. Ex. Monday 2/3 Tuesday 2/4 Wednesday 2/5 Thursday 2/6 Friday 2/7 WeektoDate Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds 11111 6.5 22222 7.0 33333 7.0 44444 7.0 55555 6.5 166665 There is then a column with the Month to date total. I have added a column after that and want it to show the matching workdays amounts. Meaning, if I have only 13 days of production so far this month I want the total of the first 13 days of production in the previous month. Both months are in the same workbook. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare & sum previous month production by workday.
Jacob, Thanks for looking at this. This almost gives me what I want. I
need a criteria where it matches workdays. ie. On the 13th workday of April I want it to give me the total pounds for the 1st 13 workdays of Mar. By the way, this also solved another issue I was having elsewhere. I like a twofer! Cindy "Jacob Skaria" wrote: Hi Cindy Suppose you have the Sheets named Jan,Feb,Mar,Apr and you are currently working on Apr. I assume you insert two columns every day for Pounds and Hours just before the WeektoDate column/ If my assumption is correct you can try the below I assume we have 5 days represented from Col A to Col K. For the pound total in the current sheet use the below formula in cell L3. =SUMPRODUCT(--($A2:K2="Pounds"),--($A3:K3)) In M3 where you need to get the details from the previous sheet ie(Mar) use the below formula =SUMPRODUCT(--(Mar!$A2:K2="Pounds"),--(Mar!$A3:K3)) For having this formula rolling; leave 1 column blank and always insert a column from col J. If this post helps click Yes --------------- Jacob Skaria "Cindy" wrote: I need a formula to compare the pound production of the current month, by workdays, with the previous month. I have turned sumif and countif every way I can and still can't get it to give me right total pounds. To complicate the problem, the spreadsheet has the days across the rows with a column for hours next to each day and then a week to date total at the end of each week. I have Excel 2007. Ex. Monday 2/3 Tuesday 2/4 Wednesday 2/5 Thursday 2/6 Friday 2/7 WeektoDate Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds 11111 6.5 22222 7.0 33333 7.0 44444 7.0 55555 6.5 166665 There is then a column with the Month to date total. I have added a column after that and want it to show the matching workdays amounts. Meaning, if I have only 13 days of production so far this month I want the total of the first 13 days of production in the previous month. Both months are in the same workbook. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare & sum previous month production by workday.
Jacob,
After playing with this off and on all day, I "think" I have worked it out. I actually ended up using your formulas for the MTD with a subtraction from a column that I added using a CountIF. It worked on my test spreadsheet and tomorrow at work I will try it on the actual on. Wish me luck. Thanks for your help!! "Jacob Skaria" wrote: Hi Cindy Suppose you have the Sheets named Jan,Feb,Mar,Apr and you are currently working on Apr. I assume you insert two columns every day for Pounds and Hours just before the WeektoDate column/ If my assumption is correct you can try the below I assume we have 5 days represented from Col A to Col K. For the pound total in the current sheet use the below formula in cell L3. =SUMPRODUCT(--($A2:K2="Pounds"),--($A3:K3)) In M3 where you need to get the details from the previous sheet ie(Mar) use the below formula =SUMPRODUCT(--(Mar!$A2:K2="Pounds"),--(Mar!$A3:K3)) For having this formula rolling; leave 1 column blank and always insert a column from col J. If this post helps click Yes --------------- Jacob Skaria "Cindy" wrote: I need a formula to compare the pound production of the current month, by workdays, with the previous month. I have turned sumif and countif every way I can and still can't get it to give me right total pounds. To complicate the problem, the spreadsheet has the days across the rows with a column for hours next to each day and then a week to date total at the end of each week. I have Excel 2007. Ex. Monday 2/3 Tuesday 2/4 Wednesday 2/5 Thursday 2/6 Friday 2/7 WeektoDate Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds 11111 6.5 22222 7.0 33333 7.0 44444 7.0 55555 6.5 166665 There is then a column with the Month to date total. I have added a column after that and want it to show the matching workdays amounts. Meaning, if I have only 13 days of production so far this month I want the total of the first 13 days of production in the previous month. Both months are in the same workbook. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare & sum previous month production by workday.
Roger, thank you for the advice and the offer of help. Actually I do
understand the difference between storing data and reporting, I have dabbled a bit in Access and wrote a small P.O./Maintenance program for our trucking company. Unfortunately, this spreadsheet is a monster that my boss likes and is not interested in changing, so...............LOL Off topic, my husband, a programmer, has said the same thing as you for years about this spreadsheet. When I read what you wrote the thought crossed my mind to ask if you were my husband. He, of course, thought it was hilarious! Cindy "Roger Govier" wrote: Hi Cindy I'm not saying it is impossible to do what you want, but it would be very complicated. Unfortunately, you have confused the storage of data, with reporting. They would be best on separate sheets. All of your data for the year (or longer) should be held on one sheet in the format Date Pounds Hours with a row for each day going down the sheet. Then you should have a Report sheet which pulls across the data that you want to see, relative to an Start date that you give at the top of the sheet. It would be quite easy to convert your existing data to the new layout with a small amount of VBA. If you are able to send me your workbook, I would be happy to convert it for you and return. To mail direct, send to roger at technology4u dot co dot uk Change the at and dots to make valid email address -- Regards Roger Govier "Cindy" wrote in message ... I need a formula to compare the pound production of the current month, by workdays, with the previous month. I have turned sumif and countif every way I can and still can't get it to give me right total pounds. To complicate the problem, the spreadsheet has the days across the rows with a column for hours next to each day and then a week to date total at the end of each week. I have Excel 2007. Ex. Monday 2/3 Tuesday 2/4 Wednesday 2/5 Thursday 2/6 Friday 2/7 WeektoDate Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds 11111 6.5 22222 7.0 33333 7.0 44444 7.0 55555 6.5 166665 There is then a column with the Month to date total. I have added a column after that and want it to show the matching workdays amounts. Meaning, if I have only 13 days of production so far this month I want the total of the first 13 days of production in the previous month. Both months are in the same workbook. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare & sum previous month production by workday.
Cindy, Happy to hear you made it; and more than happy that I could help you....
I like what you mentioned ""spreadsheet is a monster that my boss likes and is not interested in changing" If this post helps click Yes --------------- Jacob Skaria "Cindy" wrote: Jacob, After playing with this off and on all day, I "think" I have worked it out. I actually ended up using your formulas for the MTD with a subtraction from a column that I added using a CountIF. It worked on my test spreadsheet and tomorrow at work I will try it on the actual on. Wish me luck. Thanks for your help!! "Jacob Skaria" wrote: Hi Cindy Suppose you have the Sheets named Jan,Feb,Mar,Apr and you are currently working on Apr. I assume you insert two columns every day for Pounds and Hours just before the WeektoDate column/ If my assumption is correct you can try the below I assume we have 5 days represented from Col A to Col K. For the pound total in the current sheet use the below formula in cell L3. =SUMPRODUCT(--($A2:K2="Pounds"),--($A3:K3)) In M3 where you need to get the details from the previous sheet ie(Mar) use the below formula =SUMPRODUCT(--(Mar!$A2:K2="Pounds"),--(Mar!$A3:K3)) For having this formula rolling; leave 1 column blank and always insert a column from col J. If this post helps click Yes --------------- Jacob Skaria "Cindy" wrote: I need a formula to compare the pound production of the current month, by workdays, with the previous month. I have turned sumif and countif every way I can and still can't get it to give me right total pounds. To complicate the problem, the spreadsheet has the days across the rows with a column for hours next to each day and then a week to date total at the end of each week. I have Excel 2007. Ex. Monday 2/3 Tuesday 2/4 Wednesday 2/5 Thursday 2/6 Friday 2/7 WeektoDate Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds 11111 6.5 22222 7.0 33333 7.0 44444 7.0 55555 6.5 166665 There is then a column with the Month to date total. I have added a column after that and want it to show the matching workdays amounts. Meaning, if I have only 13 days of production so far this month I want the total of the first 13 days of production in the previous month. Both months are in the same workbook. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare & sum previous month production by workday.
Hi Cindy
Sorry for blaming you for your boss's muddled thinking<bg Shame he is such an Ostrich - but, you have to work with what you've got!!! Do you have your sheets always starting on a Monday, or do they start with whatever is the first workday for that month? If it is the first workday, then provided you have the Analysis Toolpak loaded, ToolsAddinsAnalysis Toolpak then you can calculate the current Workday. In a separate cell on your sheet enter the formula =NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),0), TODAY())-1 This will give the number of workdays to today, so the column number for Pounds, would have to be less that or equal to that figure * 2 Amend your SP formula =SUMPRODUCT(--(Mar!$A2:K2="Pounds"),--(Mar!$A3:K3),--(COLUMN($A3:K3)<=WD*2)) where WD represents the cell location where you have the Networkdays formula entered. The NetWorkday formula can be amended to include Holidays as well, if required. The general format is =NETWORKDAYS(Startdate,enddate,holidays) where holidays is either named range or a range like $X1:$X10, which contains a list of holiday dates. P.S. I'm not your husband<bg -- Regards Roger Govier "Cindy" wrote in message ... Roger, thank you for the advice and the offer of help. Actually I do understand the difference between storing data and reporting, I have dabbled a bit in Access and wrote a small P.O./Maintenance program for our trucking company. Unfortunately, this spreadsheet is a monster that my boss likes and is not interested in changing, so...............LOL Off topic, my husband, a programmer, has said the same thing as you for years about this spreadsheet. When I read what you wrote the thought crossed my mind to ask if you were my husband. He, of course, thought it was hilarious! Cindy "Roger Govier" wrote: Hi Cindy I'm not saying it is impossible to do what you want, but it would be very complicated. Unfortunately, you have confused the storage of data, with reporting. They would be best on separate sheets. All of your data for the year (or longer) should be held on one sheet in the format Date Pounds Hours with a row for each day going down the sheet. Then you should have a Report sheet which pulls across the data that you want to see, relative to an Start date that you give at the top of the sheet. It would be quite easy to convert your existing data to the new layout with a small amount of VBA. If you are able to send me your workbook, I would be happy to convert it for you and return. To mail direct, send to roger at technology4u dot co dot uk Change the at and dots to make valid email address -- Regards Roger Govier "Cindy" wrote in message ... I need a formula to compare the pound production of the current month, by workdays, with the previous month. I have turned sumif and countif every way I can and still can't get it to give me right total pounds. To complicate the problem, the spreadsheet has the days across the rows with a column for hours next to each day and then a week to date total at the end of each week. I have Excel 2007. Ex. Monday 2/3 Tuesday 2/4 Wednesday 2/5 Thursday 2/6 Friday 2/7 WeektoDate Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds Hours Pounds 11111 6.5 22222 7.0 33333 7.0 44444 7.0 55555 6.5 166665 There is then a column with the Month to date total. I have added a column after that and want it to show the matching workdays amounts. Meaning, if I have only 13 days of production so far this month I want the total of the first 13 days of production in the previous month. Both months are in the same workbook. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
workday with month functions | Excel Worksheet Functions | |||
Retrieve data for previous 3, 6, 12 month given current month | Excel Worksheet Functions | |||
Excel inventory Sheet for workday month | Excel Worksheet Functions | |||
Previous Month End | Excel Discussion (Misc queries) | |||
get the latest day of the previous month | Excel Discussion (Misc queries) |