+ Reply to Thread
Results 1 to 4 of 4

base formula calculation on whether different cell has formula

  1. #1
    rcmodelr
    Guest

    base formula calculation on whether different cell has formula

    I have a spreadsheet to maintain truck weights.

    If the weighmaster enters a Gross, and a Secondary Gross, the spreadsheet
    should calculate the amount of shrink (Gross - Secondary Gross).

    If the weighmaster enters ONLY a Gross and a Tare weight, the spreadsheet
    should give an ESTIMATED shrink calculation, then show the result of Gross -
    Estimated Shrink in the Secondary Gross column.

    How can I do this??? Only way I could think of is with some way to
    determine whether Secondary Gross column cell contains a USER ENTERED NUMBER,
    or still has the formula to arrive at the ESTIMATED secondary weight. If
    Secondary Gross has a formula, then Shrink should be Gross * .005, and
    Secondary Gross should show result of Gross - Shrink.

    If BOTH Gross AND Secondary Gross cells contain user entered numbers, Shrink
    should contain result of Gross - Secondary Gross.

    Btw... My employer has Excel 2000. So please, if you have a solution, try
    to make it one I can implement in Excel 2000.

  2. #2
    Bill Kuunders
    Guest

    Re: base formula calculation on whether different cell has formula

    A2 is gross
    B2 is sec gross
    C2 is shrinkage

    enter in C2

    =IF(B2>0,A2-B2,A2*0.005)

    Greetings from New Zealand
    Bill K

    "rcmodelr" <rcmodelr@discussions.microsoft.com> wrote in message
    news:017644AA-BC28-4572-B209-1CB36757E403@microsoft.com...
    >I have a spreadsheet to maintain truck weights.
    >
    > If the weighmaster enters a Gross, and a Secondary Gross, the spreadsheet
    > should calculate the amount of shrink (Gross - Secondary Gross).
    >
    > If the weighmaster enters ONLY a Gross and a Tare weight, the spreadsheet
    > should give an ESTIMATED shrink calculation, then show the result of
    > Gross -
    > Estimated Shrink in the Secondary Gross column.
    >
    > How can I do this??? Only way I could think of is with some way to
    > determine whether Secondary Gross column cell contains a USER ENTERED
    > NUMBER,
    > or still has the formula to arrive at the ESTIMATED secondary weight. If
    > Secondary Gross has a formula, then Shrink should be Gross * .005, and
    > Secondary Gross should show result of Gross - Shrink.
    >
    > If BOTH Gross AND Secondary Gross cells contain user entered numbers,
    > Shrink
    > should contain result of Gross - Secondary Gross.
    >
    > Btw... My employer has Excel 2000. So please, if you have a solution, try
    > to make it one I can implement in Excel 2000.




  3. #3
    rcmodelr
    Guest

    Re: base formula calculation on whether different cell has formula

    That would work... EXCEPT that the secondary Gross cells by default have the
    formula to subtract contents of Shrink from Gross to arrive at an Estimated
    Secondary gross. So until a secondary gross, or both a Gross AND tare weight
    are entered, Secondary Gross is Null. Forgot to include. If No secondary
    Gross is entered, the Estimated Shrink is calculated from the NET Weight (
    Gross - Tare).

    So right now, I have 2 spreadsheet setup files... One for when the scale at
    the plant is working, and a second that figures strictly an estimated shrink
    as long as the trucks need to be weighed elsewhere.

    a b C D E
    Gross Secondary Tare Net Shrink

    Default cell contents

    A Blank
    B Formula to give estimated secondary gross =A2-E2
    C Blank
    D Formula (assuming in row 2) =A2 -C2
    E. on sheet for estimated shrink =D2 * .005

    Ideally, the formula for shrink should determine if secondary Gross still
    has the default formula or user entered number since THAT would determine how
    to calculate Shrink

    If Secondary Gross is user-entered, Shrink should be Gross - Secondary Gross
    (=A2-B2)
    If Secondary Gross still contains default formula, Shrink should use
    Estimate (=D2*.005)

    If nothing else, if you know how to do this with a user defined VBA function
    that could be used in the spreadsheet, tell me.

    I already programmed the spreadsheet setup in VBA so to set up the sheet,
    the user only has to know the lot number, farm name, how many loads, and the
    catch count, and the VBA coding will set up the spreadsheet with properly
    placed total lines, grand totals, and correctly format the shift related
    summary page of the spreadsheet. So I'm by no means afraid to do this with
    an added in VBA coded spreadsheet function to test whether B2 contains a
    Formula.

    "Bill Kuunders" wrote:

    > A2 is gross
    > B2 is sec gross
    > C2 is shrinkage
    >
    > enter in C2
    >
    > =IF(B2>0,A2-B2,A2*0.005)
    >
    > Greetings from New Zealand
    > Bill K
    >
    > "rcmodelr" <rcmodelr@discussions.microsoft.com> wrote in message
    > news:017644AA-BC28-4572-B209-1CB36757E403@microsoft.com...
    > >I have a spreadsheet to maintain truck weights.
    > >
    > > If the weighmaster enters a Gross, and a Secondary Gross, the spreadsheet
    > > should calculate the amount of shrink (Gross - Secondary Gross).
    > >
    > > If the weighmaster enters ONLY a Gross and a Tare weight, the spreadsheet
    > > should give an ESTIMATED shrink calculation, then show the result of
    > > Gross -
    > > Estimated Shrink in the Secondary Gross column.
    > >
    > > How can I do this??? Only way I could think of is with some way to
    > > determine whether Secondary Gross column cell contains a USER ENTERED
    > > NUMBER,
    > > or still has the formula to arrive at the ESTIMATED secondary weight. If
    > > Secondary Gross has a formula, then Shrink should be Gross * .005, and
    > > Secondary Gross should show result of Gross - Shrink.
    > >
    > > If BOTH Gross AND Secondary Gross cells contain user entered numbers,
    > > Shrink
    > > should contain result of Gross - Secondary Gross.
    > >
    > > Btw... My employer has Excel 2000. So please, if you have a solution, try
    > > to make it one I can implement in Excel 2000.

    >
    >
    >


  4. #4
    Bill Kuunders
    Guest

    Re: base formula calculation on whether different cell has formula

    try this one

    =IF(ISFORMULA(B2),D2*0.005,A2-B2)

    worked for me
    just not sure why sec gross will display 0 unless gross and tare weight are
    entered
    --
    Greetings from New Zealand
    Bill K

    "rcmodelr" <rcmodelr@discussions.microsoft.com> wrote in message
    news:F53233F1-2430-4A18-AD8C-7FAE27B52764@microsoft.com...
    > That would work... EXCEPT that the secondary Gross cells by default have
    > the
    > formula to subtract contents of Shrink from Gross to arrive at an
    > Estimated
    > Secondary gross. So until a secondary gross, or both a Gross AND tare
    > weight
    > are entered, Secondary Gross is Null. Forgot to include. If No secondary
    > Gross is entered, the Estimated Shrink is calculated from the NET Weight (
    > Gross - Tare).
    >
    > So right now, I have 2 spreadsheet setup files... One for when the scale
    > at
    > the plant is working, and a second that figures strictly an estimated
    > shrink
    > as long as the trucks need to be weighed elsewhere.
    >
    > a b C D E
    > Gross Secondary Tare Net Shrink
    >
    > Default cell contents
    >
    > A Blank
    > B Formula to give estimated secondary gross =A2-E2
    > C Blank
    > D Formula (assuming in row 2) =A2 -C2
    > E. on sheet for estimated shrink =D2 * .005
    >
    > Ideally, the formula for shrink should determine if secondary Gross still
    > has the default formula or user entered number since THAT would determine
    > how
    > to calculate Shrink
    >
    > If Secondary Gross is user-entered, Shrink should be Gross - Secondary
    > Gross
    > (=A2-B2)
    > If Secondary Gross still contains default formula, Shrink should use
    > Estimate (=D2*.005)
    >
    > If nothing else, if you know how to do this with a user defined VBA
    > function
    > that could be used in the spreadsheet, tell me.
    >
    > I already programmed the spreadsheet setup in VBA so to set up the sheet,
    > the user only has to know the lot number, farm name, how many loads, and
    > the
    > catch count, and the VBA coding will set up the spreadsheet with properly
    > placed total lines, grand totals, and correctly format the shift related
    > summary page of the spreadsheet. So I'm by no means afraid to do this
    > with
    > an added in VBA coded spreadsheet function to test whether B2 contains a
    > Formula.
    >
    > "Bill Kuunders" wrote:
    >
    >> A2 is gross
    >> B2 is sec gross
    >> C2 is shrinkage
    >>
    >> enter in C2
    >>
    >> =IF(B2>0,A2-B2,A2*0.005)
    >>
    >> Greetings from New Zealand
    >> Bill K
    >>
    >> "rcmodelr" <rcmodelr@discussions.microsoft.com> wrote in message
    >> news:017644AA-BC28-4572-B209-1CB36757E403@microsoft.com...
    >> >I have a spreadsheet to maintain truck weights.
    >> >
    >> > If the weighmaster enters a Gross, and a Secondary Gross, the
    >> > spreadsheet
    >> > should calculate the amount of shrink (Gross - Secondary Gross).
    >> >
    >> > If the weighmaster enters ONLY a Gross and a Tare weight, the
    >> > spreadsheet
    >> > should give an ESTIMATED shrink calculation, then show the result of
    >> > Gross -
    >> > Estimated Shrink in the Secondary Gross column.
    >> >
    >> > How can I do this??? Only way I could think of is with some way to
    >> > determine whether Secondary Gross column cell contains a USER ENTERED
    >> > NUMBER,
    >> > or still has the formula to arrive at the ESTIMATED secondary weight.
    >> > If
    >> > Secondary Gross has a formula, then Shrink should be Gross * .005, and
    >> > Secondary Gross should show result of Gross - Shrink.
    >> >
    >> > If BOTH Gross AND Secondary Gross cells contain user entered numbers,
    >> > Shrink
    >> > should contain result of Gross - Secondary Gross.
    >> >
    >> > Btw... My employer has Excel 2000. So please, if you have a solution,
    >> > try
    >> > to make it one I can implement in Excel 2000.

    >>
    >>
    >>




+ 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