Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Function
I am running the followig line of code to calculate the mode(and a similar
line to calculate the average) of a series of data. Just some background, I have the sheet set up with task in columns, with sub columns MTWRF for the days of the week. I have rooms set up in the rows. Then where a row and column intersect an x is placed if the task is completed in that room on the corresponding day. The following line of code seems to correctly take the sum of the number of times per week (based on the number of x's in the row) a task is done for each respective room and then find the mode for all the rooms. I have atleast three quesions about that: 1) Using this code I can only have it find the mode of 12 rows at a time, I need to be able to take the mode of anywhere from 5 to a little over 100 rows. Is that possible? 2) Right now it is set up so a message box appears and displays "Mode for Trash...." I tried to instead set up to display the mode in a cell on the spread sheet, but when I used the code Worksheets("Sheet1").Cells(1,1).Value=lmode, no value appeared in cell A1. Is there something Im doing wrong. 3)Finally, In some of the rooms a task is done only once a week, once a month, twice a week or 7 times a week. When that is the case in the cell that corresponds to Monday for the respective task and room one of those special cases is entered in (ie "Once a Month", "7 days a Week", "Once a Week") Also each of these cases results in a set sum for the task in a room. (ie once a week=1 for the sum, once a mont=.25, 7 days a week =7.) I thought about using if statements to take that into consideration but I don't know how that would work using the Application function that I am, If you have any ideas then let me know. Scott Sub CalcModeforTrash() Dim lMode As Long Dim v(1 To 13) As Long i = 1 For Each cell In Range("G2:G14") v(i) = Application.CountA(cell.Resize(1, 5)) i = i + 1 Next lMode = Application.Mode(v) MsgBox "Mode for Trash: " & lMode End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Function
Assume Column G contains the Monday/Special case entries and that Column A
can be used to determine the extent of the data (It has the name of the rooms and nothing below the last room name. Adjust as appropriate. Sub CalcModeforTrash() Dim rng As Range Dim v() As Long Set rng = Range(Range("G2"), _ Range("G" & Cells(Rows.Count, 1).End(xlUp).Row)) ReDim v(1 To rng.Count) i = 1 For Each cell In rng Select Case LCase(cell.Text) Case "once a month" v(i) = 0.25 Case "7 days a week" v(i) = 7 Case "once a week" v(i) = 1 Case Else v(i) = Application.CountA(cell.Resize(1, 5)) End Select i = i + 1 Next Worksheets("Sheet1").Cells(1, 1).Value = Application.Mode(v) msgbox "results: " & Application.Mode(v) End Sub Make sure you look for the answer on the worksheet named Sheet1 in cell A1. -- Regards, Tom Ogilvy "sip8316" wrote in message ... I am running the followig line of code to calculate the mode(and a similar line to calculate the average) of a series of data. Just some background, I have the sheet set up with task in columns, with sub columns MTWRF for the days of the week. I have rooms set up in the rows. Then where a row and column intersect an x is placed if the task is completed in that room on the corresponding day. The following line of code seems to correctly take the sum of the number of times per week (based on the number of x's in the row) a task is done for each respective room and then find the mode for all the rooms. I have atleast three quesions about that: 1) Using this code I can only have it find the mode of 12 rows at a time, I need to be able to take the mode of anywhere from 5 to a little over 100 rows. Is that possible? 2) Right now it is set up so a message box appears and displays "Mode for Trash...." I tried to instead set up to display the mode in a cell on the spread sheet, but when I used the code Worksheets("Sheet1").Cells(1,1).Value=lmode, no value appeared in cell A1. Is there something Im doing wrong. 3)Finally, In some of the rooms a task is done only once a week, once a month, twice a week or 7 times a week. When that is the case in the cell that corresponds to Monday for the respective task and room one of those special cases is entered in (ie "Once a Month", "7 days a Week", "Once a Week") Also each of these cases results in a set sum for the task in a room. (ie once a week=1 for the sum, once a mont=.25, 7 days a week =7.) I thought about using if statements to take that into consideration but I don't know how that would work using the Application function that I am, If you have any ideas then let me know. Scott Sub CalcModeforTrash() Dim lMode As Long Dim v(1 To 13) As Long i = 1 For Each cell In Range("G2:G14") v(i) = Application.CountA(cell.Resize(1, 5)) i = i + 1 Next lMode = Application.Mode(v) MsgBox "Mode for Trash: " & lMode End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Function
I keep getting closer and closer but I seem to always have a problem. I used the code you gave and adapted it to my spreadsheet and it appeared to work 100% at first, but then I realized that no matter what the data in the sheet the answer displayed was either 0 or 5. ( So even if I change an entire row to x on the MWF so the mode should be 3, it would come out 0 or 5.) I played around alot but I don't have a ton of experience with using VBA so I wasn't able to fix it. So first off I had a couple of quesions about the code you provided. 1) This line of code: Set rng = Range(Range("G2"), _ Range("G" & Cells(Rows.Count, 1).End(xlUp).Row)) I interpreted it to be setting the range of data. Saying in column G the rows from the End all the way up to the top of the sheet is the range to be used. Is this right? If it is then I should say that my first row of actual data is the 6th row of the spreadsheet not the 1st. 2) I don't see how, or where it looks at a row and counts the number of x and uses that number for v(i)...I get the cases for once a month and so on but not the suming of hte x's. 3) This I didn't bring up before but is there a method of using an array or something so it only takes the mode of the non zero values. In the past I have done this with column of numbers by typing: =MODE(IF(A1:A100<0,A1:A100)) directly into the cell I wanted it displayed in. If you know why the mode isn't being calculate correctly I would appreciate the help. Bellow I typed a very short and simple example of what my spread sheet looks like....Hopefully that helps some. And thanks if I can pull this off it will have a huge impact on my intership. Trash Sweep Mop Dust M T W R F M T W R F M T W R F M T W R F Rm1 x x x x x x x x x x x Rm2 x x x x x x x x x Rm3 x x x x x x x x x x x x x x x Once a Week Rm4 x x x x x x x x Once a Week Rm5 Once a Week x x Once a Month Once a Week With this I want to have the code I right take the mode of each task for all the rooms (ie. the mode for Trash is 5 times/wk, for Sweep and mop is three times/wk, and dust would be 1 time/wk because only mode for the non zero cells is calculated.) Then I want the mode for each task to be displayed in its respective column. I have alot of code for this sheet already but this is the only thing I cant get to work right, if there a way to edit the code you gave me so it completes my task please let me know Thanks, Scott "Tom Ogilvy" wrote: Assume Column G contains the Monday/Special case entries and that Column A can be used to determine the extent of the data (It has the name of the rooms and nothing below the last room name. Adjust as appropriate. Sub CalcModeforTrash() Dim rng As Range Dim v() As Long Set rng = Range(Range("G2"), _ Range("G" & Cells(Rows.Count, 1).End(xlUp).Row)) ReDim v(1 To rng.Count) i = 1 For Each cell In rng Select Case LCase(cell.Text) Case "once a month" v(i) = 0.25 Case "7 days a week" v(i) = 7 Case "once a week" v(i) = 1 Case Else v(i) = Application.CountA(cell.Resize(1, 5)) End Select i = i + 1 Next Worksheets("Sheet1").Cells(1, 1).Value = Application.Mode(v) msgbox "results: " & Application.Mode(v) End Sub Make sure you look for the answer on the worksheet named Sheet1 in cell A1. -- Regards, Tom Ogilvy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Function
First, I put in data like this:
A x x x x x B x x x C once a week D x x E x x F x x G x x H once a month I x x J x x It returned 2 I extended the data: A x x x x x B x x x C once a week D x x E x x F x x G x x H once a month I x x J x x A 7 days a week B 7 days a week C 7 days a week D 7 days a week E 7 days a week F 7 days a week G 7 days a week H 7 days a week I 7 days a week J 7 days a week A 7 days a week B 7 days a week C 7 days a week D 7 days a week E 7 days a week it returned 7 I put in 29 blank rows in the middle and it returned 0 (as expected). So it appears to work fine unmodified (based on my stated assumptions). -- Regards, Tom Ogilvy "sip8316" wrote in message ... I keep getting closer and closer but I seem to always have a problem. I used the code you gave and adapted it to my spreadsheet and it appeared to work 100% at first, but then I realized that no matter what the data in the sheet the answer displayed was either 0 or 5. ( So even if I change an entire row to x on the MWF so the mode should be 3, it would come out 0 or 5.) I played around alot but I don't have a ton of experience with using VBA so I wasn't able to fix it. So first off I had a couple of quesions about the code you provided. 1) This line of code: Set rng = Range(Range("G2"), _ Range("G" & Cells(Rows.Count, 1).End(xlUp).Row)) I interpreted it to be setting the range of data. Saying in column G the rows from the End all the way up to the top of the sheet is the range to be used. Is this right? If it is then I should say that my first row of actual data is the 6th row of the spreadsheet not the 1st. 2) I don't see how, or where it looks at a row and counts the number of x and uses that number for v(i)...I get the cases for once a month and so on but not the suming of hte x's. 3) This I didn't bring up before but is there a method of using an array or something so it only takes the mode of the non zero values. In the past I have done this with column of numbers by typing: =MODE(IF(A1:A100<0,A1:A100)) directly into the cell I wanted it displayed in. If you know why the mode isn't being calculate correctly I would appreciate the help. Bellow I typed a very short and simple example of what my spread sheet looks like....Hopefully that helps some. And thanks if I can pull this off it will have a huge impact on my intership. Trash Sweep Mop Dust M T W R F M T W R F M T W R F M T W R F Rm1 x x x x x x x x x x x Rm2 x x x x x x x x x Rm3 x x x x x x x x x x x x x x x Once a Week Rm4 x x x x x x x x Once a Week Rm5 Once a Week x x Once a Month Once a Week With this I want to have the code I right take the mode of each task for all the rooms (ie. the mode for Trash is 5 times/wk, for Sweep and mop is three times/wk, and dust would be 1 time/wk because only mode for the non zero cells is calculated.) Then I want the mode for each task to be displayed in its respective column. I have alot of code for this sheet already but this is the only thing I cant get to work right, if there a way to edit the code you gave me so it completes my task please let me know Thanks, Scott "Tom Ogilvy" wrote: Assume Column G contains the Monday/Special case entries and that Column A can be used to determine the extent of the data (It has the name of the rooms and nothing below the last room name. Adjust as appropriate. Sub CalcModeforTrash() Dim rng As Range Dim v() As Long Set rng = Range(Range("G2"), _ Range("G" & Cells(Rows.Count, 1).End(xlUp).Row)) ReDim v(1 To rng.Count) i = 1 For Each cell In rng Select Case LCase(cell.Text) Case "once a month" v(i) = 0.25 Case "7 days a week" v(i) = 7 Case "once a week" v(i) = 1 Case Else v(i) = Application.CountA(cell.Resize(1, 5)) End Select i = i + 1 Next Worksheets("Sheet1").Cells(1, 1).Value = Application.Mode(v) msgbox "results: " & Application.Mode(v) End Sub Make sure you look for the answer on the worksheet named Sheet1 in cell A1. -- Regards, Tom Ogilvy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Function
Inline:
"sip8316" wrote in message ... I keep getting closer and closer but I seem to always have a problem. I used the code you gave and adapted it to my spreadsheet and it appeared to work 100% at first, but then I realized that no matter what the data in the sheet the answer displayed was either 0 or 5. ( So even if I change an entire row to x on the MWF so the mode should be 3, it would come out 0 or 5.) I played around alot but I don't have a ton of experience with using VBA so I wasn't able to fix it. So first off I had a couple of quesions about the code you provided. 1) This line of code: Set rng = Range(Range("G2"), _ Range("G" & Cells(Rows.Count, 1).End(xlUp).Row)) I interpreted it to be setting the range of data. Saying in column G the rows from the End all the way up to the top of the sheet is the range to be used. Is this right? If it is then I should say that my first row of actual data is the 6th row of the spreadsheet not the 1st. So change G2 to G6 2) I don't see how, or where it looks at a row and counts the number of x and uses that number for v(i)...I get the cases for once a month and so on but not the suming of hte x's. counts them right he v(i) = Application.CountA(cell.Resize(1, 5)) 3) This I didn't bring up before but is there a method of using an array or something so it only takes the mode of the non zero values. In the past I have done this with column of numbers by typing: =MODE(IF(A1:A100<0,A1:A100)) directly into the cell I wanted it displayed in. Just change Dim v() As Long to Dim v() as Variant and change v(i) = Application.CountA(cell.Resize(1, 5)) to if application.CountA(cell.Resize(1,5)) = 0 then v(i) = False else v(i) = countA(cell.Resize(1,5)) End if From help on the Mode Function: If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included. If you know why the mode isn't being calculate correctly I would appreciate the help. Bellow I typed a very short and simple example of what my spread sheet looks like....Hopefully that helps some. And thanks if I can pull this off it will have a huge impact on my intership. Trash Sweep Mop Dust M T W R F M T W R F M T W R F M T W R F Rm1 x x x x x x x x x x x Rm2 x x x x x x x x x Rm3 x x x x x x x x x x x x x x x Once a Week Rm4 x x x x x x x x Once a Week Rm5 Once a Week x x Once a Month Once a Week With this I want to have the code I right take the mode of each task for all the rooms (ie. the mode for Trash is 5 times/wk, for Sweep and mop is three times/wk, and dust would be 1 time/wk because only mode for the non zero cells is calculated.) Then I want the mode for each task to be displayed in its respective column. That could be done, but unfortunately I can't guess where your data is or where you want the results. You show five columns for each activity and you say you have a variable number of rows - what does in its respective column mean specifically. Where are the respective columns. What row in relation to the bottom of the data. What cell contains the first M in M T W R F columns. I have alot of code for this sheet already but this is the only thing I cant get to work right, if there a way to edit the code you gave me so it completes my task please let me know Thanks, Scott "Tom Ogilvy" wrote: Assume Column G contains the Monday/Special case entries and that Column A can be used to determine the extent of the data (It has the name of the rooms and nothing below the last room name. Adjust as appropriate. Sub CalcModeforTrash() Dim rng As Range Dim v() As Long Set rng = Range(Range("G2"), _ Range("G" & Cells(Rows.Count, 1).End(xlUp).Row)) ReDim v(1 To rng.Count) i = 1 For Each cell In rng Select Case LCase(cell.Text) Case "once a month" v(i) = 0.25 Case "7 days a week" v(i) = 7 Case "once a week" v(i) = 1 Case Else v(i) = Application.CountA(cell.Resize(1, 5)) End Select i = i + 1 Next Worksheets("Sheet1").Cells(1, 1).Value = Application.Mode(v) msgbox "results: " & Application.Mode(v) End Sub Make sure you look for the answer on the worksheet named Sheet1 in cell A1. -- Regards, Tom Ogilvy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Function
Tom thanks,
I dont' know why I couldn't make it work the first time I tried but I re pasted and copied and after adjusting it to my project it worked great And the new code to not include blank cells worked great also. Sorry for all the trouble, And thanks, Scott "Tom Ogilvy" wrote: Inline: "sip8316" wrote in message ... I keep getting closer and closer but I seem to always have a problem. I used the code you gave and adapted it to my spreadsheet and it appeared to work 100% at first, but then I realized that no matter what the data in the sheet the answer displayed was either 0 or 5. ( So even if I change an entire row to x on the MWF so the mode should be 3, it would come out 0 or 5.) I played around alot but I don't have a ton of experience with using VBA so I wasn't able to fix it. So first off I had a couple of quesions about the code you provided. 1) This line of code: Set rng = Range(Range("G2"), _ Range("G" & Cells(Rows.Count, 1).End(xlUp).Row)) I interpreted it to be setting the range of data. Saying in column G the rows from the End all the way up to the top of the sheet is the range to be used. Is this right? If it is then I should say that my first row of actual data is the 6th row of the spreadsheet not the 1st. So change G2 to G6 2) I don't see how, or where it looks at a row and counts the number of x and uses that number for v(i)...I get the cases for once a month and so on but not the suming of hte x's. counts them right he v(i) = Application.CountA(cell.Resize(1, 5)) 3) This I didn't bring up before but is there a method of using an array or something so it only takes the mode of the non zero values. In the past I have done this with column of numbers by typing: =MODE(IF(A1:A100<0,A1:A100)) directly into the cell I wanted it displayed in. Just change Dim v() As Long to Dim v() as Variant and change v(i) = Application.CountA(cell.Resize(1, 5)) to if application.CountA(cell.Resize(1,5)) = 0 then v(i) = False else v(i) = countA(cell.Resize(1,5)) End if From help on the Mode Function: If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included. If you know why the mode isn't being calculate correctly I would appreciate the help. Bellow I typed a very short and simple example of what my spread sheet looks like....Hopefully that helps some. And thanks if I can pull this off it will have a huge impact on my intership. Trash Sweep Mop Dust M T W R F M T W R F M T W R F M T W R F Rm1 x x x x x x x x x x x Rm2 x x x x x x x x x Rm3 x x x x x x x x x x x x x x x Once a Week Rm4 x x x x x x x x Once a Week Rm5 Once a Week x x Once a Month Once a Week With this I want to have the code I right take the mode of each task for all the rooms (ie. the mode for Trash is 5 times/wk, for Sweep and mop is three times/wk, and dust would be 1 time/wk because only mode for the non zero cells is calculated.) Then I want the mode for each task to be displayed in its respective column. That could be done, but unfortunately I can't guess where your data is or where you want the results. You show five columns for each activity and you say you have a variable number of rows - what does in its respective column mean specifically. Where are the respective columns. What row in relation to the bottom of the data. What cell contains the first M in M T W R F columns. I have alot of code for this sheet already but this is the only thing I cant get to work right, if there a way to edit the code you gave me so it completes my task please let me know Thanks, Scott "Tom Ogilvy" wrote: Assume Column G contains the Monday/Special case entries and that Column A can be used to determine the extent of the data (It has the name of the rooms and nothing below the last room name. Adjust as appropriate. Sub CalcModeforTrash() Dim rng As Range Dim v() As Long Set rng = Range(Range("G2"), _ Range("G" & Cells(Rows.Count, 1).End(xlUp).Row)) ReDim v(1 To rng.Count) i = 1 For Each cell In rng Select Case LCase(cell.Text) Case "once a month" v(i) = 0.25 Case "7 days a week" v(i) = 7 Case "once a week" v(i) = 1 Case Else v(i) = Application.CountA(cell.Resize(1, 5)) End Select i = i + 1 Next Worksheets("Sheet1").Cells(1, 1).Value = Application.Mode(v) msgbox "results: " & Application.Mode(v) End Sub Make sure you look for the answer on the worksheet named Sheet1 in cell A1. -- Regards, Tom Ogilvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i need help with a function application with multiple arguments | Excel Worksheet Functions | |||
Conditional Function Application | Excel Discussion (Misc queries) | |||
Application.ScreenUpdating function question | Excel Worksheet Functions | |||
application.worksheetfunction. <function (syntax) | Excel Programming | |||
Application.Calculatefull function | Excel Programming |