how do I set up the following formula in excell.

D

dmritter

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
 
J

JLatham

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.
 
J

JLatham

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.
 
D

dmritter

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.
 
J

JLatham

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
 
Top