Need Help Writing a Formula

[Lynn]

Here's my problem:

First I need A5 to be divided by 2.

Second Part: Whatever that number may be - if greater than or equal to
500 then $2.24. if less than or equal to 499 then $2.54

Third part: If first part answer is less than or equal to 250 then it
needs to add together the second part with $2.00. if the first part is
greater then 250 then it needs to be divided by 250 and that number
multiplyed by $2.00 and added to the answer to the second part for an
answer in A6.

Hope I didn't make this too confusing. Thanks!!

 

[Ian]

Lynn,

Let me see if I have this right.
If A5/2 <=250 then A6=2.54 +2 (ie 4.54)
If 250< A5/2 <=500 then A6=2.54 + A5/2/250*2
If A5/2>500 then A6= 2.24 + A5/2/250*2

If this is correct then we can simplify the formula significantly by not
dividing A5 by 2 but doubling the limits (to 500 & 1000). We then need to
double the divisor to 500, but as we are then multiplying the sum by 2 we
can just divide by 250. Does this make sense?

Try this in A6:

=IF(A5<=500,4.54,IF(A5<=1000,2.54+(A5/250),2.24+(A5/250)))

 

[Lynn]

This is working for some of the numbers I plug in, but not for all.

Example:

I plug in 700 in A5. My answer for A6 should be 6.54.

700 divded by 2 = 350 which would mean 2.54
350 divided by 250 is 1.4 (round to the next whole number (2) 2x2 =
4.00
So then I add 2.54 + 4.00 to get 6.54

Which the formula mentioned I get: 5.34.

Could it be the rounding part that is getting me?

If I plug in 1,000 in A5 I should get 6.24 in A.
With the formula I get 6.54.

Another Example:
1000 divided by 2 = 500 which would mean 2.24
500 divided by 250 is 2 (2x2 = 4.00)
So then 1 add 2.24 + 4.00 I should get 6.24.

 

[Sandy Mann]

I am sure that Ian will be along in a minute with a correction to his
formula. In the mean time does:

=2.54-(A5>=1000)*0.3+(A5<=500)*2+(A5>500)*ROUNDUP((A5/500),0)*2

work for you?

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Lynn]

no, it doesn't work either...It contains some error.

 

[Ian]

You didn't mention the need to round up the numbers along the way. Try this:

=IF(A5<=500,4.54,IF(A5<=1000,2.54+ROUNDUP(A5/500,0)*2,2.24+ROUNDUP(A5/500,0)*2))

 

[Sandy Mann]

no, it doesn't work either...It contains some error.

That is not a very descriptive reply and you don't say to whom it is that
you are replying. If it was Ian, (as I assume it was), try taking the "="
out of the "A5<=1000" part of his formula. Both formulas will then return
6.54 when 700 is plugged in and 6.24 when 1000 is plugged in which what your
examples wanted.

If you mean that with A5 empty you still get 4.54 then wrap my formula in an
IF statement:

=IF(A5=0,0,2.54-(A5>=1000)*0.3+(A5<=500)*2+(A5>500)*ROUNDUP((A5/500),0)*2)

I would however reommend that you use Ian's formula because it is easier to
read but I will leave Ian to modify his own formula to account for blank A5
if that is required.

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Lynn]

I really appreciate everything you have helped me with. Everything is
working with this, except 2 numbers...

When I plug in 1,000 and 999; I should be getting 6.24 and I get 6.54.

1,000/2 = 500 which should be 2.24
500/250 is 2*2 for 4.00

999/2 = 499.5 (in reality is 500) for 2.24
500/250 is 2*2 for 4.00

 

[Lynn]

Ian's formula is working and I did take the = of of the A5<=1000. I
really appreciate everything you all have helped me with. One last
issue and I think we got it.

For instance, when I plug in 999; I should be getting 6.24 and I get
6.54.

999/2 = 499.5 (in reality is 500) for 2.24
500/250 is 2*2 for 4.00

 

[Ian]

Try changing the 1000 to 998.

 

[Sandy Mann]

Hi Ian,

I'm glad that you are around now - I always think that it is impertinent to
suggest changes to other people's formulas, especially if they are perfectly
capable of doing so themselves. I kind of fell into it by accident, my
apologies.

--
Regards


Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Ian]

No problem Sandy. I can't guarantee being around on a regular basis so I'm
quite happy for someone to offer suggestions "on my behalf" as it were.