Finding the value over half of another value.

[Guest]

Hi

I have a three columns . In Cell A1i have 4 Units and in B1 i have 3 units.
I would like cell C1 to show the value of what ever number is left over from
cell B1 if it is more than half the value of A1. So in this case i have 4/2 =
2 So cell B2 being 3 is 1 over half of A1. so that figure would be multiply
by 4.23 so C1 =4.23.
If the value of B1 matches half or is below half the value of A1 it would
ignore the number.

Many thanks

Neil

 

[Guest]

Maybe:-

=IF(B1-(A1/2)<=0,"",(B1-(A1/2))*4.23)

Mike

 

[Guest]

Thanks Mike that works the only thing is i would like to round the half
values up. So
So if A1 =5 units and B1 =3 units in C1 i get 2.12. Iwould like to get this
figure to round up so i only get full price (4.23).

Thanks Again

 

[Guest]

Neil,

I don't understand.

If the 1/2 of A1 is rounded up then you could get spurious results.
For example:-

A1=5
B1=3

Half of A1 prior to rounding up passes your test of B1 being greater than
the value of 1/2 A1 but if the result is rounded to 3 then it fails that test.

Mike

 

[Guest]

Hi MIke

Thanks for the reponse.

Sorry i can see i am making it sound a bit confusing.
But what i mean to say is i can't use half units. So in the case of

A1=5 units
B1=3 units

They would have an allocation of 2.5 units, but as they can't be halfed it
would be 3 units. So anything above 3 units then would be multipled by 4.23.
On the even numbers it would be easier.

Sorry i can see this is a lot more difficult than i originally thought.
Thanks for the help

 

[MartinW]

Hi Niel,

This isn't going anywhere because you are only explaining bits and pieces
of what you want.

Please explain in detail what it is you are trying to achieve.

Regards
Martin

 

[Guest]

Hi Martin thanks for the reply.

What i need is to basically get unitsx4.23
But i only need to do this when the units in B1 are above half the value in
cell A1.
for example

A1 = 4 Units B1= 2 Units C1=0
C1 is o as the Units in B are half the value of the Units in A1. But if it was

A1= 4 Units B1 = 3 Units C1=4.23
C1 would be 4.23 as there is 1 Unit over the half value of A1.

The formula i was kindly given by Mike works fine except for when i have odd
numbers. As i can't charge 4.23 for half a unit. so if

A1=5 Units B1=3 Units i want C1=0 as B1 would be 2.5 half the value of A1
but i can't use half units so i would round it up to 3. And anything above
this charged at 4.23 per unit.
So if
A1=5 B1=4 C1=4.23

I hope this is clearer!
I really appreciate all the replys as i know it is a tricky one

Many Thanks

Neil

 

[MartinW]

Hi Niel,

Still not sure I'm with you but give this a try.

=IF(B1<=A1/2,"",(B1-ROUNDUP(A1/2,0))*4.23)

HTH
Martin

 

[Guest]

Hi Martin

Thanks for persisting with!
That works perfect!!!
Thank you very much you have saved me a lot of working out.

Regards

Neil

 

[MartinW]

Hi Neil,

Glad it worked, although it was Mike's solution.
I just gave his idea a little tweak.

Regards
Martin