Price of a Steer (desperate)

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I'm going to try real hard to explaine a formula question
I despertly need.

I place an order to buy cattle at 850 pounds and a price
of $1.00.
However cattle don't all come to the sale at 850 pounds.
Therefore I have a "price slide". For every 50lbs.(that
varies with the season sometimes it might be 25 lbs.)over
850 lbs. I need to reduce the price by $0.04 per lb. That
may also change from time to time.
Any animal under 850 lbs. and in 50lb. incraments I need
to pay $0.04 a lb. more. (and that changes according to
the season).

Now I generate a spread sheet from a *.csv file that has
the weights of each animal in a column and listed by row.

Is it possible to place the 3 variables 1. increment, 2.
Slide-up, and 3. Slide-down in 3 separate cells and have
the colume beside the weight calculate each animals worth?
 
Hi, Rick,
Is it possible to place the 3 variables 1. increment, 2.
Slide-up, and 3. Slide-down in 3 separate cells and have
the colume beside the weight calculate each animals worth?
Click to expand...

If I understand correctly, here are the formulas you'll need:

Column A: weight (just the value)
Column B: base rate (the value 1)
Column C: slidedown =IF(A2>850,INT((A2-850)/50)*0.04,0)
Column D: slideup =IF(A2<850,INT((850-A2)/50)*0.04,0)
Column E: rate =B2-C2+D2
Column F: total =E2*A2

These are the formulas for row 2, because I assume you will want
headers in row 1; if that's wrong, just use A1 where I've put A2, B1
for B2, etc. So, fill these formulas into row 2, then select the
entire range, columns B through F and as many rows as have weight in
column A, and Fill Down (from the Edit menu).

HTH,
--Carl
 
I think you may want at least one more "variable":

Assume:

Base Price ($1, here) in J1, Increment in J2 (50), Slideup in J3
(0.04), Slidedown in J4 (0.04)

then with weight in B2, one way:

C2: =B2*($J$1 - MAX(0,INT((850-B2)/$J$2))*$J$3 +
MAX(0,INT((B2-850)/$J$2)*$J$4))
 
Back
Top