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

Maybe:-

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

Mike

"Neil" wrote:

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

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


"Mike H" wrote:

Maybe:-

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

Mike

"Neil" wrote:

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

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

"Neil" wrote:


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


"Mike H" wrote:

Maybe:-

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

Mike

"Neil" wrote:

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

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



"Mike H" wrote:

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

"Neil" wrote:


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


"Mike H" wrote:

Maybe:-

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

Mike

"Neil" wrote:

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

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



"Neil" wrote in message
...
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



"Mike H" wrote:

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

"Neil" wrote:


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


"Mike H" wrote:

Maybe:-

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

Mike

"Neil" wrote:

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

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" wrote:

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



"Neil" wrote in message
...
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



"Mike H" wrote:

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

"Neil" wrote:


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


"Mike H" wrote:

Maybe:-

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

Mike

"Neil" wrote:

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

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


"Neil" wrote in message
...
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" wrote:

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



"Neil" wrote in message
...
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



"Mike H" wrote:

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

"Neil" wrote:


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


"Mike H" wrote:

Maybe:-

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

Mike

"Neil" wrote:

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

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" wrote:

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


"Neil" wrote in message
...
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" wrote:

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



"Neil" wrote in message
...
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



"Mike H" wrote:

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

"Neil" wrote:


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


"Mike H" wrote:

Maybe:-

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

Mike

"Neil" wrote:

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

Hi Neil,

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

Regards
Martin


"Neil" wrote in message
...
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" wrote:

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


"Neil" wrote in message
...
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" wrote:

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



"Neil" wrote in message
...
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



"Mike H" wrote:

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

"Neil" wrote:


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


"Mike H" wrote:

Maybe:-

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

Mike

"Neil" wrote:

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