Is there a way to set up multiple variables....What I mean is this:

I have a sheet set up in which in the columns I have tasks to do and in the
rows I have the rooms/buildings they have to be done in. Where they
interesect I have the number of times per week they are done (for example in
the annex trash is done 5 times/wk, in the control room 3 times/wk and so on)
The number of times per week is delegated by an x in a cell for each time.

I have it set up to add the x to find a weekly total but I want to have it
do this for each building and then to take the mode of all the buildings for
a particular task. The only way I coud think of was to make a loop that went
through for each task, and in that loop have it create a variable for
eachexisting room that contained the number of times the task had been done
(for the respective room)

Then To have another that took the sum of all those numbers and displayed
the sum somewhere on the spreadsheet. I will be doing this on many
spreadsheets with anywhere from 5 to 100 buildings.

Any help would be great.

Thanks,

Scott

You mean you have xxxxx in the cell at the intersection of the ANNEX row
and the TRASH Column

at the bottom of the trash column you would have a formula (assume the trash
column is column G and you have buildings in rows 2 through 50):

so in G51 put in the formula

=Mode(len(G2:G50))

Entered with control+Shift+Enter rather than just enter since this is an
array formula.

--
Regards,
Tom Ogilvy




"sip8316" wrote in message
...
Is there a way to set up multiple variables....What I mean is this:

I have a sheet set up in which in the columns I have tasks to do and in
the
rows I have the rooms/buildings they have to be done in. Where they
interesect I have the number of times per week they are done (for example
in
the annex trash is done 5 times/wk, in the control room 3 times/wk and so
on)
The number of times per week is delegated by an x in a cell for each
time.

I have it set up to add the x to find a weekly total but I want to have it
do this for each building and then to take the mode of all the buildings
for
a particular task. The only way I coud think of was to make a loop that
went
through for each task, and in that loop have it create a variable for
eachexisting room that contained the number of times the task had been
done
(for the respective room)

Then To have another that took the sum of all those numbers and displayed
the sum somewhere on the spreadsheet. I will be doing this on many
spreadsheets with anywhere from 5 to 100 buildings.

Any help would be great.

Thanks,

Scott

An addendum:

=Mode(len(G2:G50))

appears to work when entered normally - so you don't have to array enter it.

--
Regards,
Tom Ogilvy

"sip8316" wrote in message
...
Is there a way to set up multiple variables....What I mean is this:

I have a sheet set up in which in the columns I have tasks to do and in
the
rows I have the rooms/buildings they have to be done in. Where they
interesect I have the number of times per week they are done (for example
in
the annex trash is done 5 times/wk, in the control room 3 times/wk and so
on)
The number of times per week is delegated by an x in a cell for each
time.

I have it set up to add the x to find a weekly total but I want to have it
do this for each building and then to take the mode of all the buildings
for
a particular task. The only way I coud think of was to make a loop that
went
through for each task, and in that loop have it create a variable for
eachexisting room that contained the number of times the task had been
done
(for the respective room)

Then To have another that took the sum of all those numbers and displayed
the sum somewhere on the spreadsheet. I will be doing this on many
spreadsheets with anywhere from 5 to 100 buildings.

Any help would be great.

Thanks,

Scott

Thanks guys,

The way I typed it mislead you a little though. A more exact interpretation
to the x (or xxxxx thing) would be that I use 5 columns for each task, they
are merged for the title of the task but undernieth the title they are
separated into columns for days of the week (M T W R F) and an x is placed in
the appropriate cell if the task is done in that place on that particular
day. I can easily sum the number of x's like I said but I need the mode of
the sum of the number of x's for each building. Sorry that that makes it
more complicated, any help would be great thoguh,

thanks,

Scott

"Tom Ogilvy" wrote:

An addendum:

=Mode(len(G2:G50))

appears to work when entered normally - so you don't have to array enter it.

--
Regards,
Tom Ogilvy

"sip8316" wrote in message
...
Is there a way to set up multiple variables....What I mean is this:

I have a sheet set up in which in the columns I have tasks to do and in
the
rows I have the rooms/buildings they have to be done in. Where they
interesect I have the number of times per week they are done (for example
in
the annex trash is done 5 times/wk, in the control room 3 times/wk and so
on)
The number of times per week is delegated by an x in a cell for each
time.

I have it set up to add the x to find a weekly total but I want to have it
do this for each building and then to take the mode of all the buildings
for
a particular task. The only way I coud think of was to make a loop that
went
through for each task, and in that loop have it create a variable for
eachexisting room that contained the number of times the task had been
done
(for the respective room)

Then To have another that took the sum of all those numbers and displayed
the sum somewhere on the spreadsheet. I will be doing this on many
spreadsheets with anywhere from 5 to 100 buildings.

Any help would be great.

Thanks,

Scott

"Mode of the sum for each building"

For the purpose of your sum, does the room matter? Do you have separate
sums for each room/building (and you need to find the mode of those) or just
for the entire building?

"sip8316" wrote:

Thanks guys,

The way I typed it mislead you a little though. A more exact interpretation
to the x (or xxxxx thing) would be that I use 5 columns for each task, they
are merged for the title of the task but undernieth the title they are
separated into columns for days of the week (M T W R F) and an x is placed in
the appropriate cell if the task is done in that place on that particular
day. I can easily sum the number of x's like I said but I need the mode of
the sum of the number of x's for each building. Sorry that that makes it
more complicated, any help would be great thoguh,

thanks,

Scott

"Tom Ogilvy" wrote:

An addendum:

=Mode(len(G2:G50))

appears to work when entered normally - so you don't have to array enter it.

--
Regards,
Tom Ogilvy

"sip8316" wrote in message
...
Is there a way to set up multiple variables....What I mean is this:

I have a sheet set up in which in the columns I have tasks to do and in
the
rows I have the rooms/buildings they have to be done in. Where they
interesect I have the number of times per week they are done (for example
in
the annex trash is done 5 times/wk, in the control room 3 times/wk and so
on)
The number of times per week is delegated by an x in a cell for each
time.

I have it set up to add the x to find a weekly total but I want to have it
do this for each building and then to take the mode of all the buildings
for
a particular task. The only way I coud think of was to make a loop that
went
through for each task, and in that loop have it create a variable for
eachexisting room that contained the number of times the task had been
done
(for the respective room)

Then To have another that took the sum of all those numbers and displayed
the sum somewhere on the spreadsheet. I will be doing this on many
spreadsheets with anywhere from 5 to 100 buildings.

Any help would be great.

Thanks,

Scott

Yeah the room does matter, if I understand what your asking. I need to write
code that will take the sum of the number of x's for eash room, with each
task (which is the sum of the number of times a task is done). Then I need
to find the mode of those sums(or the mode for the amount of times a specific
task is completed in each room) I need the mode for each task determined and
then displayed on the spreadsheet. After that I dont' need the sums or mode
stored in varialbles (I will just rerun the macro when I update the sheet).

Thanks

Scott

"K Dales" wrote:

"Mode of the sum for each building"

For the purpose of your sum, does the room matter? Do you have separate
sums for each room/building (and you need to find the mode of those) or just
for the entire building?

"sip8316" wrote:

Thanks guys,

The way I typed it mislead you a little though. A more exact interpretation
to the x (or xxxxx thing) would be that I use 5 columns for each task, they
are merged for the title of the task but undernieth the title they are
separated into columns for days of the week (M T W R F) and an x is placed in
the appropriate cell if the task is done in that place on that particular
day. I can easily sum the number of x's like I said but I need the mode of
the sum of the number of x's for each building. Sorry that that makes it
more complicated, any help would be great thoguh,

thanks,

Scott

"Tom Ogilvy" wrote:

An addendum:

=Mode(len(G2:G50))

appears to work when entered normally - so you don't have to array enter it.

--
Regards,
Tom Ogilvy

"sip8316" wrote in message
...
Is there a way to set up multiple variables....What I mean is this:

I have a sheet set up in which in the columns I have tasks to do and in
the
rows I have the rooms/buildings they have to be done in. Where they
interesect I have the number of times per week they are done (for example
in
the annex trash is done 5 times/wk, in the control room 3 times/wk and so
on)
The number of times per week is delegated by an x in a cell for each
time.

I have it set up to add the x to find a weekly total but I want to have it
do this for each building and then to take the mode of all the buildings
for
a particular task. The only way I coud think of was to make a loop that
went
through for each task, and in that loop have it create a variable for
eachexisting room that contained the number of times the task had been
done
(for the respective room)

Then To have another that took the sum of all those numbers and displayed
the sum somewhere on the spreadsheet. I will be doing this on many
spreadsheets with anywhere from 5 to 100 buildings.

Any help would be great.

Thanks,

Scott

You want to run a macro to do this.

Assume Trash is in G2:K51 (50 rows) starting in row 2

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


--
Regards,
Tom Ogilvy


"sip8316" wrote in message
...
Yeah the room does matter, if I understand what your asking. I need to
write
code that will take the sum of the number of x's for eash room, with each
task (which is the sum of the number of times a task is done). Then I
need
to find the mode of those sums(or the mode for the amount of times a
specific
task is completed in each room) I need the mode for each task determined
and
then displayed on the spreadsheet. After that I dont' need the sums or
mode
stored in varialbles (I will just rerun the macro when I update the
sheet).

Thanks

Scott

"K Dales" wrote:

"Mode of the sum for each building"

For the purpose of your sum, does the room matter? Do you have separate
sums for each room/building (and you need to find the mode of those) or
just
for the entire building?

"sip8316" wrote:

Thanks guys,

The way I typed it mislead you a little though. A more exact
interpretation
to the x (or xxxxx thing) would be that I use 5 columns for each task,
they
are merged for the title of the task but undernieth the title they are
separated into columns for days of the week (M T W R F) and an x is
placed in
the appropriate cell if the task is done in that place on that
particular
day. I can easily sum the number of x's like I said but I need the
mode of
the sum of the number of x's for each building. Sorry that that makes
it
more complicated, any help would be great thoguh,

thanks,

Scott

"Tom Ogilvy" wrote:

An addendum:

=Mode(len(G2:G50))

appears to work when entered normally - so you don't have to array
enter it.

--
Regards,
Tom Ogilvy

"sip8316" wrote in message
...
Is there a way to set up multiple variables....What I mean is
this:

I have a sheet set up in which in the columns I have tasks to do
and in
the
rows I have the rooms/buildings they have to be done in. Where
they
interesect I have the number of times per week they are done (for
example
in
the annex trash is done 5 times/wk, in the control room 3 times/wk
and so
on)
The number of times per week is delegated by an x in a cell for
each
time.

I have it set up to add the x to find a weekly total but I want to
have it
do this for each building and then to take the mode of all the
buildings
for
a particular task. The only way I coud think of was to make a
loop that
went
through for each task, and in that loop have it create a variable
for
eachexisting room that contained the number of times the task had
been
done
(for the respective room)

Then To have another that took the sum of all those numbers and
displayed
the sum somewhere on the spreadsheet. I will be doing this on many
spreadsheets with anywhere from 5 to 100 buildings.

Any help would be great.

Thanks,

Scott

Thanks Tom that worked really well....I was able to tweak it so it fit my
code, the only problem I have is that you can only put a range of 12 rows
(G2:G14 as in your example) Some of my sheets have a 100 rows or even a
little more, is there a way to do that. If there is that would be great.

"Tom Ogilvy" wrote:

You want to run a macro to do this.

Assume Trash is in G2:K51 (50 rows) starting in row 2

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


--
Regards,
Tom Ogilvy


"sip8316" wrote in message
...
Yeah the room does matter, if I understand what your asking. I need to
write
code that will take the sum of the number of x's for eash room, with each
task (which is the sum of the number of times a task is done). Then I
need
to find the mode of those sums(or the mode for the amount of times a
specific
task is completed in each room) I need the mode for each task determined
and
then displayed on the spreadsheet. After that I dont' need the sums or
mode
stored in varialbles (I will just rerun the macro when I update the
sheet).

Thanks

Scott

"K Dales" wrote:

"Mode of the sum for each building"

For the purpose of your sum, does the room matter? Do you have separate
sums for each room/building (and you need to find the mode of those) or
just
for the entire building?

"sip8316" wrote:

Thanks guys,

The way I typed it mislead you a little though. A more exact
interpretation
to the x (or xxxxx thing) would be that I use 5 columns for each task,
they
are merged for the title of the task but undernieth the title they are
separated into columns for days of the week (M T W R F) and an x is
placed in
the appropriate cell if the task is done in that place on that
particular
day. I can easily sum the number of x's like I said but I need the
mode of
the sum of the number of x's for each building. Sorry that that makes
it
more complicated, any help would be great thoguh,

thanks,

Scott

"Tom Ogilvy" wrote:

An addendum:

=Mode(len(G2:G50))

appears to work when entered normally - so you don't have to array
enter it.

--
Regards,
Tom Ogilvy

"sip8316" wrote in message
...
Is there a way to set up multiple variables....What I mean is
this:

I have a sheet set up in which in the columns I have tasks to do
and in
the
rows I have the rooms/buildings they have to be done in. Where
they
interesect I have the number of times per week they are done (for
example
in
the annex trash is done 5 times/wk, in the control room 3 times/wk
and so
on)
The number of times per week is delegated by an x in a cell for
each
time.

I have it set up to add the x to find a weekly total but I want to
have it
do this for each building and then to take the mode of all the
buildings
for
a particular task. The only way I coud think of was to make a
loop that
went
through for each task, and in that loop have it create a variable
for
eachexisting room that contained the number of times the task had
been
done
(for the respective room)

Then To have another that took the sum of all those numbers and
displayed
the sum somewhere on the spreadsheet. I will be doing this on many
spreadsheets with anywhere from 5 to 100 buildings.

Any help would be great.

Thanks,

Scott

Possible solution to you later posting of an expanded question. I actually
wrote the original for 50 rows as stated, but tested it and found a typo -
so I guess when I pasted the corrected version back, I didn't correct the
code back to address the 50 rows. In any event, as written it isn't dynamic
and it appears you need it to be dynamic for rows at least - so see the
later post.


--
Regards,
Tom Ogilvy


"sip8316" wrote in message
...
Thanks Tom that worked really well....I was able to tweak it so it fit my
code, the only problem I have is that you can only put a range of 12 rows
(G2:G14 as in your example) Some of my sheets have a 100 rows or even a
little more, is there a way to do that. If there is that would be great.

"Tom Ogilvy" wrote:

You want to run a macro to do this.

Assume Trash is in G2:K51 (50 rows) starting in row 2

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


--
Regards,
Tom Ogilvy


"sip8316" wrote in message
...
Yeah the room does matter, if I understand what your asking. I need
to
write
code that will take the sum of the number of x's for eash room, with
each
task (which is the sum of the number of times a task is done). Then I
need
to find the mode of those sums(or the mode for the amount of times a
specific
task is completed in each room) I need the mode for each task
determined
and
then displayed on the spreadsheet. After that I dont' need the sums
or
mode
stored in varialbles (I will just rerun the macro when I update the
sheet).

Thanks

Scott

"K Dales" wrote:

"Mode of the sum for each building"

For the purpose of your sum, does the room matter? Do you have
separate
sums for each room/building (and you need to find the mode of those)
or
just
for the entire building?

"sip8316" wrote:

Thanks guys,

The way I typed it mislead you a little though. A more exact
interpretation
to the x (or xxxxx thing) would be that I use 5 columns for each
task,
they
are merged for the title of the task but undernieth the title they
are
separated into columns for days of the week (M T W R F) and an x
is
placed in
the appropriate cell if the task is done in that place on that
particular
day. I can easily sum the number of x's like I said but I need
the
mode of
the sum of the number of x's for each building. Sorry that that
makes
it
more complicated, any help would be great thoguh,

thanks,

Scott

"Tom Ogilvy" wrote:

An addendum:

=Mode(len(G2:G50))

appears to work when entered normally - so you don't have to
array
enter it.

--
Regards,
Tom Ogilvy

"sip8316" wrote in message
...
Is there a way to set up multiple variables....What I mean is
this:

I have a sheet set up in which in the columns I have tasks to
do
and in
the
rows I have the rooms/buildings they have to be done in.
Where
they
interesect I have the number of times per week they are done
(for
example
in
the annex trash is done 5 times/wk, in the control room 3
times/wk
and so
on)
The number of times per week is delegated by an x in a cell
for
each
time.

I have it set up to add the x to find a weekly total but I
want to
have it
do this for each building and then to take the mode of all the
buildings
for
a particular task. The only way I coud think of was to make a
loop that
went
through for each task, and in that loop have it create a
variable
for
eachexisting room that contained the number of times the task
had
been
done
(for the respective room)

Then To have another that took the sum of all those numbers
and
displayed
the sum somewhere on the spreadsheet. I will be doing this on
many
spreadsheets with anywhere from 5 to 100 buildings.

Any help would be great.

Thanks,

Scott