what formula do I use to count right-justified cells in a row?

[Duke Carey]

What Bernie was communicating is that justification or alignment isn't a
uniform attribute, certainly not something on which you should rely. Text
can be left or right aligned by formatting, or by padding with spaces. Ditto
for numbers.

Is there something a little more uniform or reliable that denotes the cells
to count or to ignore?


"Laura" wrote:

> Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
> to know how many cells are right-justified in each row. I hope that is a
> clearer explanation of what I am looking for.
> --
> Thank you for your assistance.
>
> Laura
>
>
> "Bernie Deitrick" wrote:
>
> > Laura,
> >
> > Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
> > number, the default justification is right: enter that smae number with a leading single quote (to
> > enter it as a string) and the default justification is left. But you can change either
> > justification using formatting.
> >
> > For normally entered values in non-formatted cells, if you use a formula like
> >
> > =A1+A2
> >
> > and you will get the sum of the values, whether they are strings or not, as opposed to
> >
> > =SUM(A1:A2)
> >
> > which will only sum up true numbers.
> >
> > To count the number of true numbers in a row, you can use (to count the numbers in row 1):
> >
> > =SUMPRODUCT(ISNUMBER(1:1)*1)
> >
> > Your boss needs to do a better job of designing his spreadsheet.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Laura" <[email protected]> wrote in message
> > news:[email protected]...
> > >I am working on a spreadsheet and the project manager needs me to write a
> > > formula to count right-justified cells in each row (so he will know if the
> > > information is going to be used for his calculations). I was told that Excel
> > > doesn't have anything built in to do this, but can it be done. If so, how????
> > > --
> > > Thank you for your assistance.
> > >
> > > Laura
> >
> >
> >

[Harlan Grove]

Gary's Student wrote...
....
>Sub countj()
>Dim R As Range
>countjustify = 0
>For Each R In Selection
>If R.HorizontalAlignment = xlRight Then
>countjustify = countjustify + 1
>End If
>Next
>Cells(1, 1) = countjustify
>End Sub
....

Can you justify using a Sub rather than a Function?

Also, to address Bernie's point about numbers being right-aligned by
default,


Function crj(r As Range, Optional cn As Boolean = True) As Long
Dim c As Range
For Each c In r
If c.HorizontalAlignment = xlHAlignRight _
Or (cn And VarType(c.Value2) = vbDouble _
And c.HorizontalAlignment = xlHAlignGeneral) Then
crj = crj + 1
End If
Next c
End Function

[Laura]

We have a different spreadsheet for each well we are testing. And each
spreadsheet has anywhere from 40-60 rows depending on which chemicals they
are looking for in that well. I would be willing to try a macro, I just
couldn't figure out how to do it.
--
Thank you for your assistance.

Laura


"FSt1" wrote:

> hi,
> there isn't a formula that can do this but left and right justification is a
> cell property and this can be detected with code. i am not sure how you feel
> about running a macro to do this. plus you said "in each row", which may
> present a problem. the search would be cell by cell so depending on how much
> data you have(rows and columns), the macro could take a while to run to
> complete. so how critical is knowing how many right justified cells you have?
> post back if you would like to try a macro. i'll need to know number of
> column and rows.
>
> Regards
> FSt1
>
>
> "Laura" wrote:
>
> > Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
> > to know how many cells are right-justified in each row. I hope that is a
> > clearer explanation of what I am looking for.
> > --
> > Thank you for your assistance.
> >
> > Laura
> >
> >
> > "Bernie Deitrick" wrote:
> >
> > > Laura,
> > >
> > > Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
> > > number, the default justification is right: enter that smae number with a leading single quote (to
> > > enter it as a string) and the default justification is left. But you can change either
> > > justification using formatting.
> > >
> > > For normally entered values in non-formatted cells, if you use a formula like
> > >
> > > =A1+A2
> > >
> > > and you will get the sum of the values, whether they are strings or not, as opposed to
> > >
> > > =SUM(A1:A2)
> > >
> > > which will only sum up true numbers.
> > >
> > > To count the number of true numbers in a row, you can use (to count the numbers in row 1):
> > >
> > > =SUMPRODUCT(ISNUMBER(1:1)*1)
> > >
> > > Your boss needs to do a better job of designing his spreadsheet.
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > >
> > > "Laura" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >I am working on a spreadsheet and the project manager needs me to write a
> > > > formula to count right-justified cells in each row (so he will know if the
> > > > information is going to be used for his calculations). I was told that Excel
> > > > doesn't have anything built in to do this, but can it be done. If so, how????
> > > > --
> > > > Thank you for your assistance.
> > > >
> > > > Laura
> > >
> > >
> > >

[Harlan Grove]

Bernie Deitrick wrote...
....
>To count the number of true numbers in a row, you can use (to count the numbers in row 1):
>
>=SUMPRODUCT(ISNUMBER(1:1)*1)
....

Someone has to ask - why wouldn't it be better to use

=COUNT(1:1)

?!

[Bernie Deitrick]

But if the sheet has numbers that are entered into non-formatted cells, they will be right justified
but not be counted by your sub.

HTH,
Bernie
MS Excel MVP


"Gary's Student" <[email protected]> wrote in message
news:[email protected]...
> You can't justify your boss's decisions, but you can justify cells and you
> can count the cells that you have justified. Suppose that you have a set of
> cells with text in them and have right-justified some of them. Select the
> cells in question and run:
>
> Sub countj()
> Dim R As Range
> countjustify = 0
> For Each R In Selection
> If R.HorizontalAlignment = xlRight Then
> countjustify = countjustify + 1
> End If
> Next
> Cells(1, 1) = countjustify
> End Sub
>
> This will count the number of right-justified cells and put the result in
> A1. It will even count empty cells that have been right-justified.
>
> Maybe this can be adapted to your needs (you could make a function out of it)
> --
> Gary's Student
>
>
> "Bernie Deitrick" wrote:
>
>> Laura,
>>
>> You were clear, but maybe I wasn't clear in my explanation. There are NO formulas in Excel that
>> depend on justification, so there is NO way that your boss's calculations can depend on
>> justification. The one EXCEPTION is the visual difference between numbers and text, but you
>> cannot
>> determine the apparent justification of a non-formatted cell except based on the contents.
>>
>> Perhaps you should post the formula that you boss thinks depends on justification.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Laura" <[email protected]> wrote in message
>> news:[email protected]...
>> > Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
>> > to know how many cells are right-justified in each row. I hope that is a
>> > clearer explanation of what I am looking for.
>> > --
>> > Thank you for your assistance.
>> >
>> > Laura
>> >
>> >
>> > "Bernie Deitrick" wrote:
>> >
>> >> Laura,
>> >>
>> >> Justification doesn't change the underlying cell value, so it can be misleading. If you enter
>> >> a
>> >> number, the default justification is right: enter that smae number with a leading single quote
>> >> (to
>> >> enter it as a string) and the default justification is left. But you can change either
>> >> justification using formatting.
>> >>
>> >> For normally entered values in non-formatted cells, if you use a formula like
>> >>
>> >> =A1+A2
>> >>
>> >> and you will get the sum of the values, whether they are strings or not, as opposed to
>> >>
>> >> =SUM(A1:A2)
>> >>
>> >> which will only sum up true numbers.
>> >>
>> >> To count the number of true numbers in a row, you can use (to count the numbers in row 1):
>> >>
>> >> =SUMPRODUCT(ISNUMBER(1:1)*1)
>> >>
>> >> Your boss needs to do a better job of designing his spreadsheet.
>> >>
>> >> HTH,
>> >> Bernie
>> >> MS Excel MVP
>> >>
>> >>
>> >> "Laura" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >I am working on a spreadsheet and the project manager needs me to write a
>> >> > formula to count right-justified cells in each row (so he will know if the
>> >> > information is going to be used for his calculations). I was told that Excel
>> >> > doesn't have anything built in to do this, but can it be done. If so, how????
>> >> > --
>> >> > Thank you for your assistance.
>> >> >
>> >> > Laura
>> >>
>> >>
>> >>
>>
>>
>>

[FSt1]

hi,
there isn't a formula that can do this but left and right justification is a
cell property and this can be detected with code. i am not sure how you feel
about running a macro to do this. plus you said "in each row", which may
present a problem. the search would be cell by cell so depending on how much
data you have(rows and columns), the macro could take a while to run to
complete. so how critical is knowing how many right justified cells you have?
post back if you would like to try a macro. i'll need to know number of
column and rows.

Regards
FSt1


"Laura" wrote:

> Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
> to know how many cells are right-justified in each row. I hope that is a
> clearer explanation of what I am looking for.
> --
> Thank you for your assistance.
>
> Laura
>
>
> "Bernie Deitrick" wrote:
>
> > Laura,
> >
> > Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
> > number, the default justification is right: enter that smae number with a leading single quote (to
> > enter it as a string) and the default justification is left. But you can change either
> > justification using formatting.
> >
> > For normally entered values in non-formatted cells, if you use a formula like
> >
> > =A1+A2
> >
> > and you will get the sum of the values, whether they are strings or not, as opposed to
> >
> > =SUM(A1:A2)
> >
> > which will only sum up true numbers.
> >
> > To count the number of true numbers in a row, you can use (to count the numbers in row 1):
> >
> > =SUMPRODUCT(ISNUMBER(1:1)*1)
> >
> > Your boss needs to do a better job of designing his spreadsheet.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Laura" <[email protected]> wrote in message
> > news:[email protected]...
> > >I am working on a spreadsheet and the project manager needs me to write a
> > > formula to count right-justified cells in each row (so he will know if the
> > > information is going to be used for his calculations). I was told that Excel
> > > doesn't have anything built in to do this, but can it be done. If so, how????
> > > --
> > > Thank you for your assistance.
> > >
> > > Laura
> >
> >
> >

[Gary's Student]

You can't justify your boss's decisions, but you can justify cells and you
can count the cells that you have justified. Suppose that you have a set of
cells with text in them and have right-justified some of them. Select the
cells in question and run:

Sub countj()
Dim R As Range
countjustify = 0
For Each R In Selection
If R.HorizontalAlignment = xlRight Then
countjustify = countjustify + 1
End If
Next
Cells(1, 1) = countjustify
End Sub

This will count the number of right-justified cells and put the result in
A1. It will even count empty cells that have been right-justified.

Maybe this can be adapted to your needs (you could make a function out of it)
--
Gary's Student


"Bernie Deitrick" wrote:

> Laura,
>
> You were clear, but maybe I wasn't clear in my explanation. There are NO formulas in Excel that
> depend on justification, so there is NO way that your boss's calculations can depend on
> justification. The one EXCEPTION is the visual difference between numbers and text, but you cannot
> determine the apparent justification of a non-formatted cell except based on the contents.
>
> Perhaps you should post the formula that you boss thinks depends on justification.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Laura" <[email protected]> wrote in message
> news:[email protected]...
> > Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
> > to know how many cells are right-justified in each row. I hope that is a
> > clearer explanation of what I am looking for.
> > --
> > Thank you for your assistance.
> >
> > Laura
> >
> >
> > "Bernie Deitrick" wrote:
> >
> >> Laura,
> >>
> >> Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
> >> number, the default justification is right: enter that smae number with a leading single quote
> >> (to
> >> enter it as a string) and the default justification is left. But you can change either
> >> justification using formatting.
> >>
> >> For normally entered values in non-formatted cells, if you use a formula like
> >>
> >> =A1+A2
> >>
> >> and you will get the sum of the values, whether they are strings or not, as opposed to
> >>
> >> =SUM(A1:A2)
> >>
> >> which will only sum up true numbers.
> >>
> >> To count the number of true numbers in a row, you can use (to count the numbers in row 1):
> >>
> >> =SUMPRODUCT(ISNUMBER(1:1)*1)
> >>
> >> Your boss needs to do a better job of designing his spreadsheet.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Laura" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I am working on a spreadsheet and the project manager needs me to write a
> >> > formula to count right-justified cells in each row (so he will know if the
> >> > information is going to be used for his calculations). I was told that Excel
> >> > doesn't have anything built in to do this, but can it be done. If so, how????
> >> > --
> >> > Thank you for your assistance.
> >> >
> >> > Laura
> >>
> >>
> >>
>
>
>

[Bernie Deitrick]

Laura,

You were clear, but maybe I wasn't clear in my explanation. There are NO formulas in Excel that
depend on justification, so there is NO way that your boss's calculations can depend on
justification. The one EXCEPTION is the visual difference between numbers and text, but you cannot
determine the apparent justification of a non-formatted cell except based on the contents.

Perhaps you should post the formula that you boss thinks depends on justification.

HTH,
Bernie
MS Excel MVP


"Laura" <[email protected]> wrote in message
news:[email protected]...
> Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
> to know how many cells are right-justified in each row. I hope that is a
> clearer explanation of what I am looking for.
> --
> Thank you for your assistance.
>
> Laura
>
>
> "Bernie Deitrick" wrote:
>
>> Laura,
>>
>> Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
>> number, the default justification is right: enter that smae number with a leading single quote
>> (to
>> enter it as a string) and the default justification is left. But you can change either
>> justification using formatting.
>>
>> For normally entered values in non-formatted cells, if you use a formula like
>>
>> =A1+A2
>>
>> and you will get the sum of the values, whether they are strings or not, as opposed to
>>
>> =SUM(A1:A2)
>>
>> which will only sum up true numbers.
>>
>> To count the number of true numbers in a row, you can use (to count the numbers in row 1):
>>
>> =SUMPRODUCT(ISNUMBER(1:1)*1)
>>
>> Your boss needs to do a better job of designing his spreadsheet.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Laura" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am working on a spreadsheet and the project manager needs me to write a
>> > formula to count right-justified cells in each row (so he will know if the
>> > information is going to be used for his calculations). I was told that Excel
>> > doesn't have anything built in to do this, but can it be done. If so, how????
>> > --
>> > Thank you for your assistance.
>> >
>> > Laura
>>
>>
>>

[Gary's Student]

You can't justify your boss's decisions, but you can justify cells and you
can count the cells that you have justified. Suppose that you have a set of
cells with text in them and have right-justified some of them. Select the
cells in question and run:

Sub countj()
Dim R As Range
countjustify = 0
For Each R In Selection
If R.HorizontalAlignment = xlRight Then
countjustify = countjustify + 1
End If
Next
Cells(1, 1) = countjustify
End Sub

This will count the number of right-justified cells and put the result in
A1. It will even count empty cells that have been right-justified.

Maybe this can be adapted to your needs (you could make a function out of it)
--
Gary's Student


"Bernie Deitrick" wrote:

> Laura,
>
> You were clear, but maybe I wasn't clear in my explanation. There are NO formulas in Excel that
> depend on justification, so there is NO way that your boss's calculations can depend on
> justification. The one EXCEPTION is the visual difference between numbers and text, but you cannot
> determine the apparent justification of a non-formatted cell except based on the contents.
>
> Perhaps you should post the formula that you boss thinks depends on justification.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Laura" <[email protected]> wrote in message
> news:[email protected]...
> > Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
> > to know how many cells are right-justified in each row. I hope that is a
> > clearer explanation of what I am looking for.
> > --
> > Thank you for your assistance.
> >
> > Laura
> >
> >
> > "Bernie Deitrick" wrote:
> >
> >> Laura,
> >>
> >> Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
> >> number, the default justification is right: enter that smae number with a leading single quote
> >> (to
> >> enter it as a string) and the default justification is left. But you can change either
> >> justification using formatting.
> >>
> >> For normally entered values in non-formatted cells, if you use a formula like
> >>
> >> =A1+A2
> >>
> >> and you will get the sum of the values, whether they are strings or not, as opposed to
> >>
> >> =SUM(A1:A2)
> >>
> >> which will only sum up true numbers.
> >>
> >> To count the number of true numbers in a row, you can use (to count the numbers in row 1):
> >>
> >> =SUMPRODUCT(ISNUMBER(1:1)*1)
> >>
> >> Your boss needs to do a better job of designing his spreadsheet.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Laura" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I am working on a spreadsheet and the project manager needs me to write a
> >> > formula to count right-justified cells in each row (so he will know if the
> >> > information is going to be used for his calculations). I was told that Excel
> >> > doesn't have anything built in to do this, but can it be done. If so, how????
> >> > --
> >> > Thank you for your assistance.
> >> >
> >> > Laura
> >>
> >>
> >>
>
>
>

[FSt1]

hi,
there isn't a formula that can do this but left and right justification is a
cell property and this can be detected with code. i am not sure how you feel
about running a macro to do this. plus you said "in each row", which may
present a problem. the search would be cell by cell so depending on how much
data you have(rows and columns), the macro could take a while to run to
complete. so how critical is knowing how many right justified cells you have?
post back if you would like to try a macro. i'll need to know number of
column and rows.

Regards
FSt1


"Laura" wrote:

> Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
> to know how many cells are right-justified in each row. I hope that is a
> clearer explanation of what I am looking for.
> --
> Thank you for your assistance.
>
> Laura
>
>
> "Bernie Deitrick" wrote:
>
> > Laura,
> >
> > Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
> > number, the default justification is right: enter that smae number with a leading single quote (to
> > enter it as a string) and the default justification is left. But you can change either
> > justification using formatting.
> >
> > For normally entered values in non-formatted cells, if you use a formula like
> >
> > =A1+A2
> >
> > and you will get the sum of the values, whether they are strings or not, as opposed to
> >
> > =SUM(A1:A2)
> >
> > which will only sum up true numbers.
> >
> > To count the number of true numbers in a row, you can use (to count the numbers in row 1):
> >
> > =SUMPRODUCT(ISNUMBER(1:1)*1)
> >
> > Your boss needs to do a better job of designing his spreadsheet.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Laura" <[email protected]> wrote in message
> > news:[email protected]...
> > >I am working on a spreadsheet and the project manager needs me to write a
> > > formula to count right-justified cells in each row (so he will know if the
> > > information is going to be used for his calculations). I was told that Excel
> > > doesn't have anything built in to do this, but can it be done. If so, how????
> > > --
> > > Thank you for your assistance.
> > >
> > > Laura
> >
> >
> >

[Bernie Deitrick]

Laura,

You were clear, but maybe I wasn't clear in my explanation. There are NO formulas in Excel that
depend on justification, so there is NO way that your boss's calculations can depend on
justification. The one EXCEPTION is the visual difference between numbers and text, but you cannot
determine the apparent justification of a non-formatted cell except based on the contents.

Perhaps you should post the formula that you boss thinks depends on justification.

HTH,
Bernie
MS Excel MVP


"Laura" <[email protected]> wrote in message
news:[email protected]...
> Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
> to know how many cells are right-justified in each row. I hope that is a
> clearer explanation of what I am looking for.
> --
> Thank you for your assistance.
>
> Laura
>
>
> "Bernie Deitrick" wrote:
>
>> Laura,
>>
>> Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
>> number, the default justification is right: enter that smae number with a leading single quote
>> (to
>> enter it as a string) and the default justification is left. But you can change either
>> justification using formatting.
>>
>> For normally entered values in non-formatted cells, if you use a formula like
>>
>> =A1+A2
>>
>> and you will get the sum of the values, whether they are strings or not, as opposed to
>>
>> =SUM(A1:A2)
>>
>> which will only sum up true numbers.
>>
>> To count the number of true numbers in a row, you can use (to count the numbers in row 1):
>>
>> =SUMPRODUCT(ISNUMBER(1:1)*1)
>>
>> Your boss needs to do a better job of designing his spreadsheet.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Laura" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am working on a spreadsheet and the project manager needs me to write a
>> > formula to count right-justified cells in each row (so he will know if the
>> > information is going to be used for his calculations). I was told that Excel
>> > doesn't have anything built in to do this, but can it be done. If so, how????
>> > --
>> > Thank you for your assistance.
>> >
>> > Laura
>>
>>
>>

[Bernie Deitrick]

But if the sheet has numbers that are entered into non-formatted cells, they will be right justified
but not be counted by your sub.

HTH,
Bernie
MS Excel MVP


"Gary's Student" <[email protected]> wrote in message
news:[email protected]...
> You can't justify your boss's decisions, but you can justify cells and you
> can count the cells that you have justified. Suppose that you have a set of
> cells with text in them and have right-justified some of them. Select the
> cells in question and run:
>
> Sub countj()
> Dim R As Range
> countjustify = 0
> For Each R In Selection
> If R.HorizontalAlignment = xlRight Then
> countjustify = countjustify + 1
> End If
> Next
> Cells(1, 1) = countjustify
> End Sub
>
> This will count the number of right-justified cells and put the result in
> A1. It will even count empty cells that have been right-justified.
>
> Maybe this can be adapted to your needs (you could make a function out of it)
> --
> Gary's Student
>
>
> "Bernie Deitrick" wrote:
>
>> Laura,
>>
>> You were clear, but maybe I wasn't clear in my explanation. There are NO formulas in Excel that
>> depend on justification, so there is NO way that your boss's calculations can depend on
>> justification. The one EXCEPTION is the visual difference between numbers and text, but you
>> cannot
>> determine the apparent justification of a non-formatted cell except based on the contents.
>>
>> Perhaps you should post the formula that you boss thinks depends on justification.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Laura" <[email protected]> wrote in message
>> news:[email protected]...
>> > Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
>> > to know how many cells are right-justified in each row. I hope that is a
>> > clearer explanation of what I am looking for.
>> > --
>> > Thank you for your assistance.
>> >
>> > Laura
>> >
>> >
>> > "Bernie Deitrick" wrote:
>> >
>> >> Laura,
>> >>
>> >> Justification doesn't change the underlying cell value, so it can be misleading. If you enter
>> >> a
>> >> number, the default justification is right: enter that smae number with a leading single quote
>> >> (to
>> >> enter it as a string) and the default justification is left. But you can change either
>> >> justification using formatting.
>> >>
>> >> For normally entered values in non-formatted cells, if you use a formula like
>> >>
>> >> =A1+A2
>> >>
>> >> and you will get the sum of the values, whether they are strings or not, as opposed to
>> >>
>> >> =SUM(A1:A2)
>> >>
>> >> which will only sum up true numbers.
>> >>
>> >> To count the number of true numbers in a row, you can use (to count the numbers in row 1):
>> >>
>> >> =SUMPRODUCT(ISNUMBER(1:1)*1)
>> >>
>> >> Your boss needs to do a better job of designing his spreadsheet.
>> >>
>> >> HTH,
>> >> Bernie
>> >> MS Excel MVP
>> >>
>> >>
>> >> "Laura" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >I am working on a spreadsheet and the project manager needs me to write a
>> >> > formula to count right-justified cells in each row (so he will know if the
>> >> > information is going to be used for his calculations). I was told that Excel
>> >> > doesn't have anything built in to do this, but can it be done. If so, how????
>> >> > --
>> >> > Thank you for your assistance.
>> >> >
>> >> > Laura
>> >>
>> >>
>> >>
>>
>>
>>

[Harlan Grove]

Bernie Deitrick wrote...
....
>To count the number of true numbers in a row, you can use (to count the numbers in row 1):
>
>=SUMPRODUCT(ISNUMBER(1:1)*1)
....

Someone has to ask - why wouldn't it be better to use

=COUNT(1:1)

?!

[Laura]

We have a different spreadsheet for each well we are testing. And each
spreadsheet has anywhere from 40-60 rows depending on which chemicals they
are looking for in that well. I would be willing to try a macro, I just
couldn't figure out how to do it.
--
Thank you for your assistance.

Laura


"FSt1" wrote:

> hi,
> there isn't a formula that can do this but left and right justification is a
> cell property and this can be detected with code. i am not sure how you feel
> about running a macro to do this. plus you said "in each row", which may
> present a problem. the search would be cell by cell so depending on how much
> data you have(rows and columns), the macro could take a while to run to
> complete. so how critical is knowing how many right justified cells you have?
> post back if you would like to try a macro. i'll need to know number of
> column and rows.
>
> Regards
> FSt1
>
>
> "Laura" wrote:
>
> > Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
> > to know how many cells are right-justified in each row. I hope that is a
> > clearer explanation of what I am looking for.
> > --
> > Thank you for your assistance.
> >
> > Laura
> >
> >
> > "Bernie Deitrick" wrote:
> >
> > > Laura,
> > >
> > > Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
> > > number, the default justification is right: enter that smae number with a leading single quote (to
> > > enter it as a string) and the default justification is left. But you can change either
> > > justification using formatting.
> > >
> > > For normally entered values in non-formatted cells, if you use a formula like
> > >
> > > =A1+A2
> > >
> > > and you will get the sum of the values, whether they are strings or not, as opposed to
> > >
> > > =SUM(A1:A2)
> > >
> > > which will only sum up true numbers.
> > >
> > > To count the number of true numbers in a row, you can use (to count the numbers in row 1):
> > >
> > > =SUMPRODUCT(ISNUMBER(1:1)*1)
> > >
> > > Your boss needs to do a better job of designing his spreadsheet.
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > >
> > > "Laura" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >I am working on a spreadsheet and the project manager needs me to write a
> > > > formula to count right-justified cells in each row (so he will know if the
> > > > information is going to be used for his calculations). I was told that Excel
> > > > doesn't have anything built in to do this, but can it be done. If so, how????
> > > > --
> > > > Thank you for your assistance.
> > > >
> > > > Laura
> > >
> > >
> > >

[Duke Carey]

What Bernie was communicating is that justification or alignment isn't a
uniform attribute, certainly not something on which you should rely. Text
can be left or right aligned by formatting, or by padding with spaces. Ditto
for numbers.

Is there something a little more uniform or reliable that denotes the cells
to count or to ignore?


"Laura" wrote:

> Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
> to know how many cells are right-justified in each row. I hope that is a
> clearer explanation of what I am looking for.
> --
> Thank you for your assistance.
>
> Laura
>
>
> "Bernie Deitrick" wrote:
>
> > Laura,
> >
> > Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
> > number, the default justification is right: enter that smae number with a leading single quote (to
> > enter it as a string) and the default justification is left. But you can change either
> > justification using formatting.
> >
> > For normally entered values in non-formatted cells, if you use a formula like
> >
> > =A1+A2
> >
> > and you will get the sum of the values, whether they are strings or not, as opposed to
> >
> > =SUM(A1:A2)
> >
> > which will only sum up true numbers.
> >
> > To count the number of true numbers in a row, you can use (to count the numbers in row 1):
> >
> > =SUMPRODUCT(ISNUMBER(1:1)*1)
> >
> > Your boss needs to do a better job of designing his spreadsheet.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Laura" <[email protected]> wrote in message
> > news:[email protected]...
> > >I am working on a spreadsheet and the project manager needs me to write a
> > > formula to count right-justified cells in each row (so he will know if the
> > > information is going to be used for his calculations). I was told that Excel
> > > doesn't have anything built in to do this, but can it be done. If so, how????
> > > --
> > > Thank you for your assistance.
> > >
> > > Laura
> >
> >
> >

[Harlan Grove]

Gary's Student wrote...
....
>Sub countj()
>Dim R As Range
>countjustify = 0
>For Each R In Selection
>If R.HorizontalAlignment = xlRight Then
>countjustify = countjustify + 1
>End If
>Next
>Cells(1, 1) = countjustify
>End Sub
....

Can you justify using a Sub rather than a Function?

Also, to address Bernie's point about numbers being right-aligned by
default,


Function crj(r As Range, Optional cn As Boolean = True) As Long
Dim c As Range
For Each c In r
If c.HorizontalAlignment = xlHAlignRight _
Or (cn And VarType(c.Value2) = vbDouble _
And c.HorizontalAlignment = xlHAlignGeneral) Then
crj = crj + 1
End If
Next c
End Function

[Gary's Student]

You can't justify your boss's decisions, but you can justify cells and you
can count the cells that you have justified. Suppose that you have a set of
cells with text in them and have right-justified some of them. Select the
cells in question and run:

Sub countj()
Dim R As Range
countjustify = 0
For Each R In Selection
If R.HorizontalAlignment = xlRight Then
countjustify = countjustify + 1
End If
Next
Cells(1, 1) = countjustify
End Sub

This will count the number of right-justified cells and put the result in
A1. It will even count empty cells that have been right-justified.

Maybe this can be adapted to your needs (you could make a function out of it)
--
Gary's Student


"Bernie Deitrick" wrote:

> Laura,
>
> You were clear, but maybe I wasn't clear in my explanation. There are NO formulas in Excel that
> depend on justification, so there is NO way that your boss's calculations can depend on
> justification. The one EXCEPTION is the visual difference between numbers and text, but you cannot
> determine the apparent justification of a non-formatted cell except based on the contents.
>
> Perhaps you should post the formula that you boss thinks depends on justification.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Laura" <[email protected]> wrote in message
> news:[email protected]...
> > Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
> > to know how many cells are right-justified in each row. I hope that is a
> > clearer explanation of what I am looking for.
> > --
> > Thank you for your assistance.
> >
> > Laura
> >
> >
> > "Bernie Deitrick" wrote:
> >
> >> Laura,
> >>
> >> Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
> >> number, the default justification is right: enter that smae number with a leading single quote
> >> (to
> >> enter it as a string) and the default justification is left. But you can change either
> >> justification using formatting.
> >>
> >> For normally entered values in non-formatted cells, if you use a formula like
> >>
> >> =A1+A2
> >>
> >> and you will get the sum of the values, whether they are strings or not, as opposed to
> >>
> >> =SUM(A1:A2)
> >>
> >> which will only sum up true numbers.
> >>
> >> To count the number of true numbers in a row, you can use (to count the numbers in row 1):
> >>
> >> =SUMPRODUCT(ISNUMBER(1:1)*1)
> >>
> >> Your boss needs to do a better job of designing his spreadsheet.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Laura" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I am working on a spreadsheet and the project manager needs me to write a
> >> > formula to count right-justified cells in each row (so he will know if the
> >> > information is going to be used for his calculations). I was told that Excel
> >> > doesn't have anything built in to do this, but can it be done. If so, how????
> >> > --
> >> > Thank you for your assistance.
> >> >
> >> > Laura
> >>
> >>
> >>
>
>
>

[Harlan Grove]

Gary's Student wrote...
....
>Sub countj()
>Dim R As Range
>countjustify = 0
>For Each R In Selection
>If R.HorizontalAlignment = xlRight Then
>countjustify = countjustify + 1
>End If
>Next
>Cells(1, 1) = countjustify
>End Sub
....

Can you justify using a Sub rather than a Function?

Also, to address Bernie's point about numbers being right-aligned by
default,


Function crj(r As Range, Optional cn As Boolean = True) As Long
Dim c As Range
For Each c In r
If c.HorizontalAlignment = xlHAlignRight _
Or (cn And VarType(c.Value2) = vbDouble _
And c.HorizontalAlignment = xlHAlignGeneral) Then
crj = crj + 1
End If
Next c
End Function

[Laura]

We have a different spreadsheet for each well we are testing. And each
spreadsheet has anywhere from 40-60 rows depending on which chemicals they
are looking for in that well. I would be willing to try a macro, I just
couldn't figure out how to do it.
--
Thank you for your assistance.

Laura


"FSt1" wrote:

> hi,
> there isn't a formula that can do this but left and right justification is a
> cell property and this can be detected with code. i am not sure how you feel
> about running a macro to do this. plus you said "in each row", which may
> present a problem. the search would be cell by cell so depending on how much
> data you have(rows and columns), the macro could take a while to run to
> complete. so how critical is knowing how many right justified cells you have?
> post back if you would like to try a macro. i'll need to know number of
> column and rows.
>
> Regards
> FSt1
>
>
> "Laura" wrote:
>
> > Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
> > to know how many cells are right-justified in each row. I hope that is a
> > clearer explanation of what I am looking for.
> > --
> > Thank you for your assistance.
> >
> > Laura
> >
> >
> > "Bernie Deitrick" wrote:
> >
> > > Laura,
> > >
> > > Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
> > > number, the default justification is right: enter that smae number with a leading single quote (to
> > > enter it as a string) and the default justification is left. But you can change either
> > > justification using formatting.
> > >
> > > For normally entered values in non-formatted cells, if you use a formula like
> > >
> > > =A1+A2
> > >
> > > and you will get the sum of the values, whether they are strings or not, as opposed to
> > >
> > > =SUM(A1:A2)
> > >
> > > which will only sum up true numbers.
> > >
> > > To count the number of true numbers in a row, you can use (to count the numbers in row 1):
> > >
> > > =SUMPRODUCT(ISNUMBER(1:1)*1)
> > >
> > > Your boss needs to do a better job of designing his spreadsheet.
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > >
> > > "Laura" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >I am working on a spreadsheet and the project manager needs me to write a
> > > > formula to count right-justified cells in each row (so he will know if the
> > > > information is going to be used for his calculations). I was told that Excel
> > > > doesn't have anything built in to do this, but can it be done. If so, how????
> > > > --
> > > > Thank you for your assistance.
> > > >
> > > > Laura
> > >
> > >
> > >

[Harlan Grove]

Bernie Deitrick wrote...
....
>To count the number of true numbers in a row, you can use (to count the numbers in row 1):
>
>=SUMPRODUCT(ISNUMBER(1:1)*1)
....

Someone has to ask - why wouldn't it be better to use

=COUNT(1:1)

?!

[Bernie Deitrick]

But if the sheet has numbers that are entered into non-formatted cells, they will be right justified
but not be counted by your sub.

HTH,
Bernie
MS Excel MVP


"Gary's Student" <[email protected]> wrote in message
news:[email protected]...
> You can't justify your boss's decisions, but you can justify cells and you
> can count the cells that you have justified. Suppose that you have a set of
> cells with text in them and have right-justified some of them. Select the
> cells in question and run:
>
> Sub countj()
> Dim R As Range
> countjustify = 0
> For Each R In Selection
> If R.HorizontalAlignment = xlRight Then
> countjustify = countjustify + 1
> End If
> Next
> Cells(1, 1) = countjustify
> End Sub
>
> This will count the number of right-justified cells and put the result in
> A1. It will even count empty cells that have been right-justified.
>
> Maybe this can be adapted to your needs (you could make a function out of it)
> --
> Gary's Student
>
>
> "Bernie Deitrick" wrote:
>
>> Laura,
>>
>> You were clear, but maybe I wasn't clear in my explanation. There are NO formulas in Excel that
>> depend on justification, so there is NO way that your boss's calculations can depend on
>> justification. The one EXCEPTION is the visual difference between numbers and text, but you
>> cannot
>> determine the apparent justification of a non-formatted cell except based on the contents.
>>
>> Perhaps you should post the formula that you boss thinks depends on justification.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Laura" <[email protected]> wrote in message
>> news:[email protected]...
>> > Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
>> > to know how many cells are right-justified in each row. I hope that is a
>> > clearer explanation of what I am looking for.
>> > --
>> > Thank you for your assistance.
>> >
>> > Laura
>> >
>> >
>> > "Bernie Deitrick" wrote:
>> >
>> >> Laura,
>> >>
>> >> Justification doesn't change the underlying cell value, so it can be misleading. If you enter
>> >> a
>> >> number, the default justification is right: enter that smae number with a leading single quote
>> >> (to
>> >> enter it as a string) and the default justification is left. But you can change either
>> >> justification using formatting.
>> >>
>> >> For normally entered values in non-formatted cells, if you use a formula like
>> >>
>> >> =A1+A2
>> >>
>> >> and you will get the sum of the values, whether they are strings or not, as opposed to
>> >>
>> >> =SUM(A1:A2)
>> >>
>> >> which will only sum up true numbers.
>> >>
>> >> To count the number of true numbers in a row, you can use (to count the numbers in row 1):
>> >>
>> >> =SUMPRODUCT(ISNUMBER(1:1)*1)
>> >>
>> >> Your boss needs to do a better job of designing his spreadsheet.
>> >>
>> >> HTH,
>> >> Bernie
>> >> MS Excel MVP
>> >>
>> >>
>> >> "Laura" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >I am working on a spreadsheet and the project manager needs me to write a
>> >> > formula to count right-justified cells in each row (so he will know if the
>> >> > information is going to be used for his calculations). I was told that Excel
>> >> > doesn't have anything built in to do this, but can it be done. If so, how????
>> >> > --
>> >> > Thank you for your assistance.
>> >> >
>> >> > Laura
>> >>
>> >>
>> >>
>>
>>
>>

[Laura]

I am working on a spreadsheet and the project manager needs me to write a
formula to count right-justified cells in each row (so he will know if the
information is going to be used for his calculations). I was told that Excel
doesn't have anything built in to do this, but can it be done. If so, how????
--
Thank you for your assistance.

Laura

[FSt1]

hi,
there isn't a formula that can do this but left and right justification is a
cell property and this can be detected with code. i am not sure how you feel
about running a macro to do this. plus you said "in each row", which may
present a problem. the search would be cell by cell so depending on how much
data you have(rows and columns), the macro could take a while to run to
complete. so how critical is knowing how many right justified cells you have?
post back if you would like to try a macro. i'll need to know number of
column and rows.

Regards
FSt1


"Laura" wrote:

> Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
> to know how many cells are right-justified in each row. I hope that is a
> clearer explanation of what I am looking for.
> --
> Thank you for your assistance.
>
> Laura
>
>
> "Bernie Deitrick" wrote:
>
> > Laura,
> >
> > Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
> > number, the default justification is right: enter that smae number with a leading single quote (to
> > enter it as a string) and the default justification is left. But you can change either
> > justification using formatting.
> >
> > For normally entered values in non-formatted cells, if you use a formula like
> >
> > =A1+A2
> >
> > and you will get the sum of the values, whether they are strings or not, as opposed to
> >
> > =SUM(A1:A2)
> >
> > which will only sum up true numbers.
> >
> > To count the number of true numbers in a row, you can use (to count the numbers in row 1):
> >
> > =SUMPRODUCT(ISNUMBER(1:1)*1)
> >
> > Your boss needs to do a better job of designing his spreadsheet.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Laura" <[email protected]> wrote in message
> > news:[email protected]...
> > >I am working on a spreadsheet and the project manager needs me to write a
> > > formula to count right-justified cells in each row (so he will know if the
> > > information is going to be used for his calculations). I was told that Excel
> > > doesn't have anything built in to do this, but can it be done. If so, how????
> > > --
> > > Thank you for your assistance.
> > >
> > > Laura
> >
> >
> >

[Duke Carey]

What Bernie was communicating is that justification or alignment isn't a
uniform attribute, certainly not something on which you should rely. Text
can be left or right aligned by formatting, or by padding with spaces. Ditto
for numbers.

Is there something a little more uniform or reliable that denotes the cells
to count or to ignore?


"Laura" wrote:

> Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
> to know how many cells are right-justified in each row. I hope that is a
> clearer explanation of what I am looking for.
> --
> Thank you for your assistance.
>
> Laura
>
>
> "Bernie Deitrick" wrote:
>
> > Laura,
> >
> > Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
> > number, the default justification is right: enter that smae number with a leading single quote (to
> > enter it as a string) and the default justification is left. But you can change either
> > justification using formatting.
> >
> > For normally entered values in non-formatted cells, if you use a formula like
> >
> > =A1+A2
> >
> > and you will get the sum of the values, whether they are strings or not, as opposed to
> >
> > =SUM(A1:A2)
> >
> > which will only sum up true numbers.
> >
> > To count the number of true numbers in a row, you can use (to count the numbers in row 1):
> >
> > =SUMPRODUCT(ISNUMBER(1:1)*1)
> >
> > Your boss needs to do a better job of designing his spreadsheet.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Laura" <[email protected]> wrote in message
> > news:[email protected]...
> > >I am working on a spreadsheet and the project manager needs me to write a
> > > formula to count right-justified cells in each row (so he will know if the
> > > information is going to be used for his calculations). I was told that Excel
> > > doesn't have anything built in to do this, but can it be done. If so, how????
> > > --
> > > Thank you for your assistance.
> > >
> > > Laura
> >
> >
> >

[Bernie Deitrick]

Laura,

Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
number, the default justification is right: enter that smae number with a leading single quote (to
enter it as a string) and the default justification is left. But you can change either
justification using formatting.

For normally entered values in non-formatted cells, if you use a formula like

=A1+A2

and you will get the sum of the values, whether they are strings or not, as opposed to

=SUM(A1:A2)

which will only sum up true numbers.

To count the number of true numbers in a row, you can use (to count the numbers in row 1):

=SUMPRODUCT(ISNUMBER(1:1)*1)

Your boss needs to do a better job of designing his spreadsheet.

HTH,
Bernie
MS Excel MVP


"Laura" <[email protected]> wrote in message
news:[email protected]...
>I am working on a spreadsheet and the project manager needs me to write a
> formula to count right-justified cells in each row (so he will know if the
> information is going to be used for his calculations). I was told that Excel
> doesn't have anything built in to do this, but can it be done. If so, how????
> --
> Thank you for your assistance.
>
> Laura

[Laura]

Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
to know how many cells are right-justified in each row. I hope that is a
clearer explanation of what I am looking for.
--
Thank you for your assistance.

Laura


"Bernie Deitrick" wrote:

> Laura,
>
> Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
> number, the default justification is right: enter that smae number with a leading single quote (to
> enter it as a string) and the default justification is left. But you can change either
> justification using formatting.
>
> For normally entered values in non-formatted cells, if you use a formula like
>
> =A1+A2
>
> and you will get the sum of the values, whether they are strings or not, as opposed to
>
> =SUM(A1:A2)
>
> which will only sum up true numbers.
>
> To count the number of true numbers in a row, you can use (to count the numbers in row 1):
>
> =SUMPRODUCT(ISNUMBER(1:1)*1)
>
> Your boss needs to do a better job of designing his spreadsheet.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Laura" <[email protected]> wrote in message
> news:[email protected]...
> >I am working on a spreadsheet and the project manager needs me to write a
> > formula to count right-justified cells in each row (so he will know if the
> > information is going to be used for his calculations). I was told that Excel
> > doesn't have anything built in to do this, but can it be done. If so, how????
> > --
> > Thank you for your assistance.
> >
> > Laura
>
>
>

[Bernie Deitrick]

Laura,

You were clear, but maybe I wasn't clear in my explanation. There are NO formulas in Excel that
depend on justification, so there is NO way that your boss's calculations can depend on
justification. The one EXCEPTION is the visual difference between numbers and text, but you cannot
determine the apparent justification of a non-formatted cell except based on the contents.

Perhaps you should post the formula that you boss thinks depends on justification.

HTH,
Bernie
MS Excel MVP


"Laura" <[email protected]> wrote in message
news:[email protected]...
> Maybe I wasn't clear . . . I don't need to sum the total of the cells I need
> to know how many cells are right-justified in each row. I hope that is a
> clearer explanation of what I am looking for.
> --
> Thank you for your assistance.
>
> Laura
>
>
> "Bernie Deitrick" wrote:
>
>> Laura,
>>
>> Justification doesn't change the underlying cell value, so it can be misleading. If you enter a
>> number, the default justification is right: enter that smae number with a leading single quote
>> (to
>> enter it as a string) and the default justification is left. But you can change either
>> justification using formatting.
>>
>> For normally entered values in non-formatted cells, if you use a formula like
>>
>> =A1+A2
>>
>> and you will get the sum of the values, whether they are strings or not, as opposed to
>>
>> =SUM(A1:A2)
>>
>> which will only sum up true numbers.
>>
>> To count the number of true numbers in a row, you can use (to count the numbers in row 1):
>>
>> =SUMPRODUCT(ISNUMBER(1:1)*1)
>>
>> Your boss needs to do a better job of designing his spreadsheet.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Laura" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am working on a spreadsheet and the project manager needs me to write a
>> > formula to count right-justified cells in each row (so he will know if the
>> > information is going to be used for his calculations). I was told that Excel
>> > doesn't have anything built in to do this, but can it be done. If so, how????
>> > --
>> > Thank you for your assistance.
>> >
>> > Laura
>>
>>
>>