Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I set up the following formula in excell.
i need to figure out the following for every claim i receive and i would like
help putting in a formula A. 62,660 *.956 = 59903 *factor or - 3133 =56770 * factor 0r - 3236 = 53534 *0 .18 = $936.12 that is part A Part B 0.1528/ 0.18 =0.849 if anyone could please help me that would be wonderful |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I set up the following formula in excell.
Could I ask that you rephrase, or describe the needs a little differently.
I'm having a problem determining just what it is you need. Also, don't use anything that might be misunderstood as a math symbol except when it is a math symbol (dash can be confused with minus sign or subtraction). Part B is easy, I think: formula in cell would be =.1528/.18 will display the result (0.84888889) can be formatted to display 0.849. Back to part A, maybe describe something like this: if claim is greater than or equal to (=) some value Then do this with it: claim * .956 * factor, OR if do this with claim amount... perform another test: if claim - amount = some other value Then do this ... describe another calculation continue on in that fashion. Don't worry if you have to use some other test like is less than or is equal to, that's fine. If you don't know the symbols, just spell it out, we'll do our best to figure it out. Use as many IFs, Thens and Ors as it takes. "dmritter" wrote: i need to figure out the following for every claim i receive and i would like help putting in a formula A. 62,660 *.956 = 59903 *factor or - 3133 =56770 * factor 0r - 3236 = 53534 *0 .18 = $936.12 that is part A Part B 0.1528/ 0.18 =0.849 if anyone could please help me that would be wonderful |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I set up the following formula in excell.
Alternative ways to deal with part B:
if either or both of the values are in cells somewhere else, you can use their addresses to perform the calculation. Case #1: the .1528 is calculated and shows up in cell A1, but .18 is a fixed value, then this formula would get the result (can go in any cell except A1): =A1*.18 Case #2: both values are calculated somewhere else, one of them is shown in cell A1, the other is in B1, then in any other cell (can't be in A1 or B1) you could put =A1*B1 and get the result. "dmritter" wrote: i need to figure out the following for every claim i receive and i would like help putting in a formula A. 62,660 *.956 = 59903 *factor or - 3133 =56770 * factor 0r - 3236 = 53534 *0 .18 = $936.12 that is part A Part B 0.1528/ 0.18 =0.849 if anyone could please help me that would be wonderful |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I set up the following formula in excell.
it is for beans part A is as follows: Net delivery weight (62,660) times
foreign material matter (.956) equals pounds(59903) times either mositure factor or minus pounds of shrink (3133) equals new pounds of beans (56770) times Pick factor OR if pounds of shrink known (3236) equals pounds of good beans (53534) times Market price of beans to cents (0.18)equals the value of the total orginal pounds to cents($9636.12) minus the elevator pick charge to grower($418.81) equals the final $ value of beans ($9154.31) dividved by orginal delivery weight less foreign material (59903) (see very first part of equation) equals the actual price received for orginal deilvery weight by cents four decimal places. Part B (0.1528) divided by current market price of beans (0.18) equals quality factorto three decimals (.849) this figure is moved to a production worksheet for final analysis. "JLatham" wrote: Could I ask that you rephrase, or describe the needs a little differently. I'm having a problem determining just what it is you need. Also, don't use anything that might be misunderstood as a math symbol except when it is a math symbol (dash can be confused with minus sign or subtraction). Part B is easy, I think: formula in cell would be =.1528/.18 will display the result (0.84888889) can be formatted to display 0.849. Back to part A, maybe describe something like this: if claim is greater than or equal to (=) some value Then do this with it: claim * .956 * factor, OR if do this with claim amount... perform another test: if claim - amount = some other value Then do this ... describe another calculation continue on in that fashion. Don't worry if you have to use some other test like is less than or is equal to, that's fine. If you don't know the symbols, just spell it out, we'll do our best to figure it out. Use as many IFs, Thens and Ors as it takes. "dmritter" wrote: i need to figure out the following for every claim i receive and i would like help putting in a formula A. 62,660 *.956 = 59903 *factor or - 3133 =56770 * factor 0r - 3236 = 53534 *0 .18 = $936.12 that is part A Part B 0.1528/ 0.18 =0.849 if anyone could please help me that would be wonderful |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I set up the following formula in excell.
I'm a bit confused at one or two points, which I'll point out later, but I
think this will do what you want. I'm going to show everything as entered into Row 2 beginning with Column A and working across the row. This leaves Row 1 for labels/titles: A2 62660 B2 ..956 C2 =Round(A2*B2,0) D2 (moisture factor, actually enter value or 0, we will use zero here) 0 E2 3133 F2 =IF(D2=0,C2-E2,C2*D2) or perhaps =IF(D2=0,C2-E2,C2-(C2*D2)) G2 (pick factor - like moisture, enter value or zero, using zero) 0 H2 3236 I2 =IF(G2=0,F2-H2,F2*G2) or, again like moisture factor, maybe should be =IF(G2=0,F2-H2,F2-(F2*G2)) J2 ..18 K2 =I2*J2 L2 $418.81 M2 =K2-L2 (in M2 I came up with $9217.31 vs your answer, believe 9217.31 is correct) N2 =M2/C2 (now my factor is $0.15387 to 5 decimals, .1539 to 4) in O2 =N2/J2 my quality factor to 3 decimals is $0.855 Reason I showed two possible formulas at F2 and I2 is that I don't know what those factors look like or how they're used. The first formula would be as if they were a relatively large value, say .9x interpreted to mean percentage good, but if they are small value like .0x or .1x, and interpreted to mean percentage bad, then the 2nd formula would be the one to use. workbook with all of this in it at: http://www.jlathamsite.com/uploads/for_dmritter.xls "dmritter" wrote: it is for beans part A is as follows: Net delivery weight (62,660) times foreign material matter (.956) equals pounds(59903) times either mositure factor or minus pounds of shrink (3133) equals new pounds of beans (56770) times Pick factor OR if pounds of shrink known (3236) equals pounds of good beans (53534) times Market price of beans to cents (0.18)equals the value of the total orginal pounds to cents($9636.12) minus the elevator pick charge to grower($418.81) equals the final $ value of beans ($9154.31) dividved by orginal delivery weight less foreign material (59903) (see very first part of equation) equals the actual price received for orginal deilvery weight by cents four decimal places. Part B (0.1528) divided by current market price of beans (0.18) equals quality factorto three decimals (.849) this figure is moved to a production worksheet for final analysis. "JLatham" wrote: Could I ask that you rephrase, or describe the needs a little differently. I'm having a problem determining just what it is you need. Also, don't use anything that might be misunderstood as a math symbol except when it is a math symbol (dash can be confused with minus sign or subtraction). Part B is easy, I think: formula in cell would be =.1528/.18 will display the result (0.84888889) can be formatted to display 0.849. Back to part A, maybe describe something like this: if claim is greater than or equal to (=) some value Then do this with it: claim * .956 * factor, OR if do this with claim amount... perform another test: if claim - amount = some other value Then do this ... describe another calculation continue on in that fashion. Don't worry if you have to use some other test like is less than or is equal to, that's fine. If you don't know the symbols, just spell it out, we'll do our best to figure it out. Use as many IFs, Thens and Ors as it takes. "dmritter" wrote: i need to figure out the following for every claim i receive and i would like help putting in a formula A. 62,660 *.956 = 59903 *factor or - 3133 =56770 * factor 0r - 3236 = 53534 *0 .18 = $936.12 that is part A Part B 0.1528/ 0.18 =0.849 if anyone could please help me that would be wonderful |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I set up the following formula in excell.
thank you so very much. this is wonderful. could you please suggest a class
on line where i could learn this techinique. "JLatham" wrote: I'm a bit confused at one or two points, which I'll point out later, but I think this will do what you want. I'm going to show everything as entered into Row 2 beginning with Column A and working across the row. This leaves Row 1 for labels/titles: A2 62660 B2 .956 C2 =Round(A2*B2,0) D2 (moisture factor, actually enter value or 0, we will use zero here) 0 E2 3133 F2 =IF(D2=0,C2-E2,C2*D2) or perhaps =IF(D2=0,C2-E2,C2-(C2*D2)) G2 (pick factor - like moisture, enter value or zero, using zero) 0 H2 3236 I2 =IF(G2=0,F2-H2,F2*G2) or, again like moisture factor, maybe should be =IF(G2=0,F2-H2,F2-(F2*G2)) J2 .18 K2 =I2*J2 L2 $418.81 M2 =K2-L2 (in M2 I came up with $9217.31 vs your answer, believe 9217.31 is correct) N2 =M2/C2 (now my factor is $0.15387 to 5 decimals, .1539 to 4) in O2 =N2/J2 my quality factor to 3 decimals is $0.855 Reason I showed two possible formulas at F2 and I2 is that I don't know what those factors look like or how they're used. The first formula would be as if they were a relatively large value, say .9x interpreted to mean percentage good, but if they are small value like .0x or .1x, and interpreted to mean percentage bad, then the 2nd formula would be the one to use. workbook with all of this in it at: http://www.jlathamsite.com/uploads/for_dmritter.xls "dmritter" wrote: it is for beans part A is as follows: Net delivery weight (62,660) times foreign material matter (.956) equals pounds(59903) times either mositure factor or minus pounds of shrink (3133) equals new pounds of beans (56770) times Pick factor OR if pounds of shrink known (3236) equals pounds of good beans (53534) times Market price of beans to cents (0.18)equals the value of the total orginal pounds to cents($9636.12) minus the elevator pick charge to grower($418.81) equals the final $ value of beans ($9154.31) dividved by orginal delivery weight less foreign material (59903) (see very first part of equation) equals the actual price received for orginal deilvery weight by cents four decimal places. Part B (0.1528) divided by current market price of beans (0.18) equals quality factorto three decimals (.849) this figure is moved to a production worksheet for final analysis. "JLatham" wrote: Could I ask that you rephrase, or describe the needs a little differently. I'm having a problem determining just what it is you need. Also, don't use anything that might be misunderstood as a math symbol except when it is a math symbol (dash can be confused with minus sign or subtraction). Part B is easy, I think: formula in cell would be =.1528/.18 will display the result (0.84888889) can be formatted to display 0.849. Back to part A, maybe describe something like this: if claim is greater than or equal to (=) some value Then do this with it: claim * .956 * factor, OR if do this with claim amount... perform another test: if claim - amount = some other value Then do this ... describe another calculation continue on in that fashion. Don't worry if you have to use some other test like is less than or is equal to, that's fine. If you don't know the symbols, just spell it out, we'll do our best to figure it out. Use as many IFs, Thens and Ors as it takes. "dmritter" wrote: i need to figure out the following for every claim i receive and i would like help putting in a formula A. 62,660 *.956 = 59903 *factor or - 3133 =56770 * factor 0r - 3236 = 53534 *0 .18 = $936.12 that is part A Part B 0.1528/ 0.18 =0.849 if anyone could please help me that would be wonderful |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I set up the following formula in excell.
One place to start would be at
http://office.microsoft.com/en-us/tr...831141033.aspx Also, for the novice, one of the better learning and reference books is the .... for Dummies series, like Excel 2003 for Dummies. There are some good introductory level tutorials with workbooks available through this page: http://www.jlathamsite.com/LearningPage.htm "dmritter" wrote: thank you so very much. this is wonderful. could you please suggest a class on line where i could learn this techinique. "JLatham" wrote: I'm a bit confused at one or two points, which I'll point out later, but I think this will do what you want. I'm going to show everything as entered into Row 2 beginning with Column A and working across the row. This leaves Row 1 for labels/titles: A2 62660 B2 .956 C2 =Round(A2*B2,0) D2 (moisture factor, actually enter value or 0, we will use zero here) 0 E2 3133 F2 =IF(D2=0,C2-E2,C2*D2) or perhaps =IF(D2=0,C2-E2,C2-(C2*D2)) G2 (pick factor - like moisture, enter value or zero, using zero) 0 H2 3236 I2 =IF(G2=0,F2-H2,F2*G2) or, again like moisture factor, maybe should be =IF(G2=0,F2-H2,F2-(F2*G2)) J2 .18 K2 =I2*J2 L2 $418.81 M2 =K2-L2 (in M2 I came up with $9217.31 vs your answer, believe 9217.31 is correct) N2 =M2/C2 (now my factor is $0.15387 to 5 decimals, .1539 to 4) in O2 =N2/J2 my quality factor to 3 decimals is $0.855 Reason I showed two possible formulas at F2 and I2 is that I don't know what those factors look like or how they're used. The first formula would be as if they were a relatively large value, say .9x interpreted to mean percentage good, but if they are small value like .0x or .1x, and interpreted to mean percentage bad, then the 2nd formula would be the one to use. workbook with all of this in it at: http://www.jlathamsite.com/uploads/for_dmritter.xls "dmritter" wrote: it is for beans part A is as follows: Net delivery weight (62,660) times foreign material matter (.956) equals pounds(59903) times either mositure factor or minus pounds of shrink (3133) equals new pounds of beans (56770) times Pick factor OR if pounds of shrink known (3236) equals pounds of good beans (53534) times Market price of beans to cents (0.18)equals the value of the total orginal pounds to cents($9636.12) minus the elevator pick charge to grower($418.81) equals the final $ value of beans ($9154.31) dividved by orginal delivery weight less foreign material (59903) (see very first part of equation) equals the actual price received for orginal deilvery weight by cents four decimal places. Part B (0.1528) divided by current market price of beans (0.18) equals quality factorto three decimals (.849) this figure is moved to a production worksheet for final analysis. "JLatham" wrote: Could I ask that you rephrase, or describe the needs a little differently. I'm having a problem determining just what it is you need. Also, don't use anything that might be misunderstood as a math symbol except when it is a math symbol (dash can be confused with minus sign or subtraction). Part B is easy, I think: formula in cell would be =.1528/.18 will display the result (0.84888889) can be formatted to display 0.849. Back to part A, maybe describe something like this: if claim is greater than or equal to (=) some value Then do this with it: claim * .956 * factor, OR if do this with claim amount... perform another test: if claim - amount = some other value Then do this ... describe another calculation continue on in that fashion. Don't worry if you have to use some other test like is less than or is equal to, that's fine. If you don't know the symbols, just spell it out, we'll do our best to figure it out. Use as many IFs, Thens and Ors as it takes. "dmritter" wrote: i need to figure out the following for every claim i receive and i would like help putting in a formula A. 62,660 *.956 = 59903 *factor or - 3133 =56770 * factor 0r - 3236 = 53534 *0 .18 = $936.12 that is part A Part B 0.1528/ 0.18 =0.849 if anyone could please help me that would be wonderful |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Reusing formula | Excel Discussion (Misc queries) | |||
formula - excell | Excel Worksheet Functions | |||
Why would a formula stop working in an Excell spreadsheet? | Excel Worksheet Functions | |||
Formula in Excell | Excel Worksheet Functions |