+ Reply to Thread
Results 1 to 27 of 27

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

  1. #1
    Duke Carey
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    > > news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    > > >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

    > >
    > >
    > >


  2. #2
    Bernie Deitrick
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    news:C16833F0-E35C-45AE-91FE-E1D3D571274C@microsoft.com...
    > 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" <lragsdale@geo-logic.com> wrote in message
    >> news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    >> >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

    >>
    >>
    >>




  3. #3
    Gary's Student
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    > news:C16833F0-E35C-45AE-91FE-E1D3D571274C@microsoft.com...
    > > 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" <lragsdale@geo-logic.com> wrote in message
    > >> news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    > >> >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
    > >>
    > >>
    > >>

    >
    >
    >


  4. #4
    FSt1
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    > > news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    > > >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

    > >
    > >
    > >


  5. #5
    Bernie Deitrick
    Guest

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

    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" <GarysStudent@discussions.microsoft.com> wrote in message
    news:59B33E9E-38EA-49A3-9FC8-29E6999973B0@microsoft.com...
    > 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" <lragsdale@geo-logic.com> wrote in message
    >> news:C16833F0-E35C-45AE-91FE-E1D3D571274C@microsoft.com...
    >> > 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" <lragsdale@geo-logic.com> wrote in message
    >> >> news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    >> >> >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
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




  6. #6
    Harlan Grove
    Guest

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

    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)

    ?!


  7. #7
    Laura
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    > > > news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    > > > >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
    > > >
    > > >
    > > >


  8. #8
    Harlan Grove
    Guest

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

    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


  9. #9
    Duke Carey
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    > > news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    > > >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

    > >
    > >
    > >


  10. #10
    Bernie Deitrick
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    news:C16833F0-E35C-45AE-91FE-E1D3D571274C@microsoft.com...
    > 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" <lragsdale@geo-logic.com> wrote in message
    >> news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    >> >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

    >>
    >>
    >>




  11. #11
    Gary's Student
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    > news:C16833F0-E35C-45AE-91FE-E1D3D571274C@microsoft.com...
    > > 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" <lragsdale@geo-logic.com> wrote in message
    > >> news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    > >> >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
    > >>
    > >>
    > >>

    >
    >
    >


  12. #12
    FSt1
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    > > news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    > > >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

    > >
    > >
    > >


  13. #13
    Bernie Deitrick
    Guest

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

    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" <GarysStudent@discussions.microsoft.com> wrote in message
    news:59B33E9E-38EA-49A3-9FC8-29E6999973B0@microsoft.com...
    > 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" <lragsdale@geo-logic.com> wrote in message
    >> news:C16833F0-E35C-45AE-91FE-E1D3D571274C@microsoft.com...
    >> > 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" <lragsdale@geo-logic.com> wrote in message
    >> >> news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    >> >> >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
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




  14. #14
    Harlan Grove
    Guest

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

    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)

    ?!


  15. #15
    Laura
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    > > > news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    > > > >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
    > > >
    > > >
    > > >


  16. #16
    Harlan Grove
    Guest

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

    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


  17. #17
    Laura
    Guest

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

    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

  18. #18
    Bernie Deitrick
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    >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




  19. #19
    Laura
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    > news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    > >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

    >
    >
    >


  20. #20
    Duke Carey
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    > > news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    > > >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

    > >
    > >
    > >


  21. #21
    Bernie Deitrick
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    news:C16833F0-E35C-45AE-91FE-E1D3D571274C@microsoft.com...
    > 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" <lragsdale@geo-logic.com> wrote in message
    >> news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    >> >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

    >>
    >>
    >>




  22. #22
    Gary's Student
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    > news:C16833F0-E35C-45AE-91FE-E1D3D571274C@microsoft.com...
    > > 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" <lragsdale@geo-logic.com> wrote in message
    > >> news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    > >> >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
    > >>
    > >>
    > >>

    >
    >
    >


  23. #23
    FSt1
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    > > news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    > > >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

    > >
    > >
    > >


  24. #24
    Bernie Deitrick
    Guest

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

    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" <GarysStudent@discussions.microsoft.com> wrote in message
    news:59B33E9E-38EA-49A3-9FC8-29E6999973B0@microsoft.com...
    > 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" <lragsdale@geo-logic.com> wrote in message
    >> news:C16833F0-E35C-45AE-91FE-E1D3D571274C@microsoft.com...
    >> > 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" <lragsdale@geo-logic.com> wrote in message
    >> >> news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    >> >> >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
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




  25. #25
    Harlan Grove
    Guest

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

    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)

    ?!


  26. #26
    Laura
    Guest

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

    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" <lragsdale@geo-logic.com> wrote in message
    > > > news:AA5BB120-71C3-461E-BA96-40830D76FD1D@microsoft.com...
    > > > >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
    > > >
    > > >
    > > >


  27. #27
    Harlan Grove
    Guest

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

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1