Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
base formula calculation on whether different cell has formula
A2 is gross
B2 is sec gross C2 is shrinkage enter in C2 =IF(B20,A2-B2,A2*0.005) Greetings from New Zealand Bill K "rcmodelr" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(B20,A2-B2,A2*0.005) Greetings from New Zealand Bill K "rcmodelr" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" 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(B20,A2-B2,A2*0.005) Greetings from New Zealand Bill K "rcmodelr" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i combine text and formula calculation in same cell | Excel Worksheet Functions | |||
fixed cell in formula calculation | Excel Worksheet Functions | |||
How to leave a formula cell blank when it has no calculation to make | Excel Discussion (Misc queries) | |||
wrong calculation in excel? formula outcome < cell value?? | Excel Worksheet Functions | |||
CALCULATION OF A CELL THAT CONTAINS A FORMULA | Excel Discussion (Misc queries) |