## Re: base formula calculation on whether different cell has formula

Sending this again as it did not show in the group first time.
"Bill Kuunders" <bill.kuunders@xtra.co.nz> wrote in message news:...
> 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
>> 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
>>> > should calculate the amount of shrink (Gross - Secondary Gross).
>>> >
>>> > If the weighmaster enters ONLY a Gross and a Tare weight, the
>>> > 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.
>>>
>>>
>>>

>
>