Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skipping blank cells in simple math formula
Help, I need a formula for work that does the following,
In a 12 sheet excel workbook, each month of the year is represented in a generic 31 row sheet. No data is entered in the rows representing weekends or holidays our office is closed. It's simple math concerning 3 cells, but the tricky part is one of the 3 cells the formula must use is in the previous working day's row. The problem is I don't know how to tell the formula to look at the previous working day's cell since on Monday's that would be 3 rows above and on the beginning of the month, that would be the last cell-row with data in it on the previous month's sheet, and then there's holidays too where rows are skipped-blank. I.E. (formula is cell C2 =A2 -B2 +A1) In this example, Row 2 would represent the 2nd of May. This formula works only if May 1st was a workday - has data in it (cell A1). However if the 1st does not have data, because it was a holiday, or weekend, then I need a smarter formula that knows to go to the last business day's row, which in this case would be on a different sheet in the workbook. Thank-you for any help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skipping blank cells in simple math formula
Difficult problem. Could try to do in VBA but a worksheet solution might be
easier. Can you create a calculated column for your worksheets? If so, I would make a column I would call "Last workday" or something like that. You need to track what is the last workday's value for what is in column A so you could use a formula like this one (written arbitrarily for the worksheet "APRIL" cell D20): =IF(ROW()=1,MARCH!$D$31,IF(OFFSET(A20,-1,0)="",OFFSET(D20,-1,0),OFFSET(A20,-1,0))) (explanation: if on row 1, will pick up the last workday value from the prior month's sheet (row 31); if the value in A for "yesterday" is blank, then it will look at the prior D value and copy this as the "last workday" value, carrying it over on the weekend/holidays; if yesterday's A is not blank then just use that. The ROW and OFFSET formulas are in there so the same formula works for every cell in column D; this also means you can use it for February and the months with 30 days to fill in the remaining rows of the sheet carrying over the value so D31 always contains the last workday's value from the month. Hope that all makes sense, but I have tested it and it seems to handle the situation. Use your D column value wherever you need the "last workday" value from A. So, for example, the formula you gave becomes: C2 = A2 - B2 + D2 -- - K Dales "jimtmcdaniels" wrote: Help, I need a formula for work that does the following, In a 12 sheet excel workbook, each month of the year is represented in a generic 31 row sheet. No data is entered in the rows representing weekends or holidays our office is closed. It's simple math concerning 3 cells, but the tricky part is one of the 3 cells the formula must use is in the previous working day's row. The problem is I don't know how to tell the formula to look at the previous working day's cell since on Monday's that would be 3 rows above and on the beginning of the month, that would be the last cell-row with data in it on the previous month's sheet, and then there's holidays too where rows are skipped-blank. I.E. (formula is cell C2 =A2 -B2 +A1) In this example, Row 2 would represent the 2nd of May. This formula works only if May 1st was a workday - has data in it (cell A1). However if the 1st does not have data, because it was a holiday, or weekend, then I need a smarter formula that knows to go to the last business day's row, which in this case would be on a different sheet in the workbook. Thank-you for any help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skipping blank cells in simple math formula
Hmm, a little too hasty! Correction needed on the part that looks at the
prior month: =IF(ROW()=1,IF(MARCH!$A$31="",MARCH!$D$31,MARCH!$A $31),IF(OFFSET(A20,-1,0)="",OFFSET(D20,-1,0),OFFSET(A20,-1,0))) -- - K Dales "jimtmcdaniels" wrote: Help, I need a formula for work that does the following, In a 12 sheet excel workbook, each month of the year is represented in a generic 31 row sheet. No data is entered in the rows representing weekends or holidays our office is closed. It's simple math concerning 3 cells, but the tricky part is one of the 3 cells the formula must use is in the previous working day's row. The problem is I don't know how to tell the formula to look at the previous working day's cell since on Monday's that would be 3 rows above and on the beginning of the month, that would be the last cell-row with data in it on the previous month's sheet, and then there's holidays too where rows are skipped-blank. I.E. (formula is cell C2 =A2 -B2 +A1) In this example, Row 2 would represent the 2nd of May. This formula works only if May 1st was a workday - has data in it (cell A1). However if the 1st does not have data, because it was a holiday, or weekend, then I need a smarter formula that knows to go to the last business day's row, which in this case would be on a different sheet in the workbook. Thank-you for any help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skipping blank cells in simple math formula
A UDF might be simpler to implement.
Eg: '##################################### Function DoCalc(a as range, b as range, c as range) do while len(a.value)=0 set a=a.offset(-1,0) loop DoCalc=a+b+c end if '###################################### The disadvantage would be that if the "a" range is blank and it has to offset before calculating, then your function might not refresh if the offset cell's value changes. Adding "volatile" to the function should fix that (with a small cost in recalculation time) -- Tim Williams Palo Alto, CA "K Dales" wrote in message ... Difficult problem. Could try to do in VBA but a worksheet solution might be easier. Can you create a calculated column for your worksheets? If so, I would make a column I would call "Last workday" or something like that. You need to track what is the last workday's value for what is in column A so you could use a formula like this one (written arbitrarily for the worksheet "APRIL" cell D20): =IF(ROW()=1,MARCH!$D$31,IF(OFFSET(A20,-1,0)="",OFFSET(D20,-1,0),OFFSET(A20,-1,0))) (explanation: if on row 1, will pick up the last workday value from the prior month's sheet (row 31); if the value in A for "yesterday" is blank, then it will look at the prior D value and copy this as the "last workday" value, carrying it over on the weekend/holidays; if yesterday's A is not blank then just use that. The ROW and OFFSET formulas are in there so the same formula works for every cell in column D; this also means you can use it for February and the months with 30 days to fill in the remaining rows of the sheet carrying over the value so D31 always contains the last workday's value from the month. Hope that all makes sense, but I have tested it and it seems to handle the situation. Use your D column value wherever you need the "last workday" value from A. So, for example, the formula you gave becomes: C2 = A2 - B2 + D2 -- - K Dales "jimtmcdaniels" wrote: Help, I need a formula for work that does the following, In a 12 sheet excel workbook, each month of the year is represented in a generic 31 row sheet. No data is entered in the rows representing weekends or holidays our office is closed. It's simple math concerning 3 cells, but the tricky part is one of the 3 cells the formula must use is in the previous working day's row. The problem is I don't know how to tell the formula to look at the previous working day's cell since on Monday's that would be 3 rows above and on the beginning of the month, that would be the last cell-row with data in it on the previous month's sheet, and then there's holidays too where rows are skipped-blank. I.E. (formula is cell C2 =A2 -B2 +A1) In this example, Row 2 would represent the 2nd of May. This formula works only if May 1st was a workday - has data in it (cell A1). However if the 1st does not have data, because it was a holiday, or weekend, then I need a smarter formula that knows to go to the last business day's row, which in this case would be on a different sheet in the workbook. Thank-you for any help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skipping blank cells in simple math formula
Crap - missed that part about the previous month. That would require a bit more coding to skip to the previous sheet if the value
of a.row got below a certain value. -- Tim Williams Palo Alto, CA "Tim Williams" <timjwilliams at gmail dot com wrote in message ... A UDF might be simpler to implement. Eg: '##################################### Function DoCalc(a as range, b as range, c as range) do while len(a.value)=0 set a=a.offset(-1,0) loop DoCalc=a+b+c end if '###################################### The disadvantage would be that if the "a" range is blank and it has to offset before calculating, then your function might not refresh if the offset cell's value changes. Adding "volatile" to the function should fix that (with a small cost in recalculation time) -- Tim Williams Palo Alto, CA "K Dales" wrote in message ... Difficult problem. Could try to do in VBA but a worksheet solution might be easier. Can you create a calculated column for your worksheets? If so, I would make a column I would call "Last workday" or something like that. You need to track what is the last workday's value for what is in column A so you could use a formula like this one (written arbitrarily for the worksheet "APRIL" cell D20): =IF(ROW()=1,MARCH!$D$31,IF(OFFSET(A20,-1,0)="",OFFSET(D20,-1,0),OFFSET(A20,-1,0))) (explanation: if on row 1, will pick up the last workday value from the prior month's sheet (row 31); if the value in A for "yesterday" is blank, then it will look at the prior D value and copy this as the "last workday" value, carrying it over on the weekend/holidays; if yesterday's A is not blank then just use that. The ROW and OFFSET formulas are in there so the same formula works for every cell in column D; this also means you can use it for February and the months with 30 days to fill in the remaining rows of the sheet carrying over the value so D31 always contains the last workday's value from the month. Hope that all makes sense, but I have tested it and it seems to handle the situation. Use your D column value wherever you need the "last workday" value from A. So, for example, the formula you gave becomes: C2 = A2 - B2 + D2 -- - K Dales "jimtmcdaniels" wrote: Help, I need a formula for work that does the following, In a 12 sheet excel workbook, each month of the year is represented in a generic 31 row sheet. No data is entered in the rows representing weekends or holidays our office is closed. It's simple math concerning 3 cells, but the tricky part is one of the 3 cells the formula must use is in the previous working day's row. The problem is I don't know how to tell the formula to look at the previous working day's cell since on Monday's that would be 3 rows above and on the beginning of the month, that would be the last cell-row with data in it on the previous month's sheet, and then there's holidays too where rows are skipped-blank. I.E. (formula is cell C2 =A2 -B2 +A1) In this example, Row 2 would represent the 2nd of May. This formula works only if May 1st was a workday - has data in it (cell A1). However if the 1st does not have data, because it was a holiday, or weekend, then I need a smarter formula that knows to go to the last business day's row, which in this case would be on a different sheet in the workbook. Thank-you for any help! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skipping blank cells in simple math formula
This works for me in a workbook set up with sequential monthly sheets.
It will keep skipping back in time until it finds a non-empty input for "a". The rest of the calculation (last line) you should be able to adapt to suit. Tim '################################################# #### Function DoCalc(a As Range, b As Range, c As Range) Const FIRST_ROW As Integer = 4 Const LAST_ROW As Integer = 34 Application.Volatile Dim indx indx = a.Parent.Index Do While Len(a.Value) = 0 If a.Row FIRST_ROW Then Set a = a.Offset(-1, 0) Else If indx 1 Then indx = indx - 1 Set a = ThisWorkbook.Sheets(indx).Cells(LAST_ROW, a.Column) Else DoCalc = "No data!" Exit Function End If End If Loop DoCalc = a & " " & b & " " & c End Function '################################################# ##### "Tim Williams" <timjwilliams at gmail dot com wrote in message ... Crap - missed that part about the previous month. That would require a bit more coding to skip to the previous sheet if the value of a.row got below a certain value. -- Tim Williams Palo Alto, CA "Tim Williams" <timjwilliams at gmail dot com wrote in message ... A UDF might be simpler to implement. Eg: '##################################### Function DoCalc(a as range, b as range, c as range) do while len(a.value)=0 set a=a.offset(-1,0) loop DoCalc=a+b+c end if '###################################### The disadvantage would be that if the "a" range is blank and it has to offset before calculating, then your function might not refresh if the offset cell's value changes. Adding "volatile" to the function should fix that (with a small cost in recalculation time) -- Tim Williams Palo Alto, CA "K Dales" wrote in message ... Difficult problem. Could try to do in VBA but a worksheet solution might be easier. Can you create a calculated column for your worksheets? If so, I would make a column I would call "Last workday" or something like that. You need to track what is the last workday's value for what is in column A so you could use a formula like this one (written arbitrarily for the worksheet "APRIL" cell D20): =IF(ROW()=1,MARCH!$D$31,IF(OFFSET(A20,-1,0)="",OFFSET(D20,-1,0),OFFSET(A20,-1,0))) (explanation: if on row 1, will pick up the last workday value from the prior month's sheet (row 31); if the value in A for "yesterday" is blank, then it will look at the prior D value and copy this as the "last workday" value, carrying it over on the weekend/holidays; if yesterday's A is not blank then just use that. The ROW and OFFSET formulas are in there so the same formula works for every cell in column D; this also means you can use it for February and the months with 30 days to fill in the remaining rows of the sheet carrying over the value so D31 always contains the last workday's value from the month. Hope that all makes sense, but I have tested it and it seems to handle the situation. Use your D column value wherever you need the "last workday" value from A. So, for example, the formula you gave becomes: C2 = A2 - B2 + D2 -- - K Dales "jimtmcdaniels" wrote: Help, I need a formula for work that does the following, In a 12 sheet excel workbook, each month of the year is represented in a generic 31 row sheet. No data is entered in the rows representing weekends or holidays our office is closed. It's simple math concerning 3 cells, but the tricky part is one of the 3 cells the formula must use is in the previous working day's row. The problem is I don't know how to tell the formula to look at the previous working day's cell since on Monday's that would be 3 rows above and on the beginning of the month, that would be the last cell-row with data in it on the previous month's sheet, and then there's holidays too where rows are skipped-blank. I.E. (formula is cell C2 =A2 -B2 +A1) In this example, Row 2 would represent the 2nd of May. This formula works only if May 1st was a workday - has data in it (cell A1). However if the 1st does not have data, because it was a holiday, or weekend, then I need a smarter formula that knows to go to the last business day's row, which in this case would be on a different sheet in the workbook. Thank-you for any help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a simple math formula | New Users to Excel | |||
simple math formula | Excel Discussion (Misc queries) | |||
Concatenation and skipping blank cells | Excel Worksheet Functions | |||
Skipping Blank Cells | Excel Discussion (Misc queries) | |||
simple formula with blank cells | Excel Worksheet Functions |