hi i'm trying to calculate charges at different rates according to the
criteria in various cells, but i'm not getting very far. example column A
contains a stock figure column B contains stock to be received figure &
column C contains type of stock to be received either a Y or a blank. what i
need is if C contains "Y" charge B at 0.05 per unit regardless of what is in
A. but if C is blank charge B at 0.025 per unit but only if A is greater than
0. hope that all makes sense & i haven't over complicated what i want to do.
any help would be appreciated.

Try this in D2 (assuming data starts on row 2):

=IF(C2="Y",B2*0.05,IF(A20,B2*0.025,""))

Hope this helps.

Pete

On Jul 5, 12:38*pm, mahone wrote:
hi i'm trying to calculate charges at different rates according to the
criteria in various cells, but i'm not getting very far. example column A
contains a stock figure column B contains stock to be received figure &
column C contains type of stock to be received either a Y or a blank. what i
need is if C contains "Y" charge B at 0.05 per unit regardless of what is in
A. but if C is blank charge B at 0.025 per unit but only if A is greater than
0. hope that all makes sense & i haven't over complicated what i want to do.
any help would be appreciated.

thanks i'll give this a go when i get back to work next week. hopefully it'll
work & i'll learnt something else.

"Pete_UK" wrote:

Try this in D2 (assuming data starts on row 2):

=IF(C2="Y",B2*0.05,IF(A20,B2*0.025,""))

Hope this helps.

Pete

On Jul 5, 12:38 pm, mahone wrote:
hi i'm trying to calculate charges at different rates according to the
criteria in various cells, but i'm not getting very far. example column A
contains a stock figure column B contains stock to be received figure &
column C contains type of stock to be received either a Y or a blank. what i
need is if C contains "Y" charge B at 0.05 per unit regardless of what is in
A. but if C is blank charge B at 0.025 per unit but only if A is greater than
0. hope that all makes sense & i haven't over complicated what i want to do.
any help would be appreciated.

You're welcome.

Pete

On Jul 6, 10:29*am, mahone wrote:
thanks i'll give this a go when i get back to work next week. hopefully it'll
work & i'll learnt something else.

the first part worked charging at the higher rate if there was a "Y" in
column C just the last bit didn't work. it charged at the lower rate when
column C was blank & there was a zero in column A instead of any quantity
greater than zero. any suggestions?

"Pete_UK" wrote:

You're welcome.

Pete

On Jul 6, 10:29 am, mahone wrote:
thanks i'll give this a go when i get back to work next week. hopefully it'll
work & i'll learnt something else.

Just check the second part of the formula, i.e.:

IF(A20,B2*0.025,"")

This relates to C2 being blank (as you said it can only contain a "Y"
or a blank) and it only multiplies B2 by 0.025 if A2 is greater than
zero - did you perhaps type < instead of ?

Failing that, post your formula here.

Pete


On Jul 7, 5:41*pm, mahone wrote:
the first part worked charging at the higher rate if there was a "Y" in
column C just the last bit didn't work. it charged at the lower rate when
column C was blank & there was a zero in column A instead of any quantity
greater than zero. any suggestions?



"Pete_UK" wrote:
You're welcome.

Pete

On Jul 6, 10:29 am, mahone wrote:
thanks i'll give this a go when i get back to work next week. hopefully it'll
work & i'll learnt something else.- Hide quoted text -

- Show quoted text -

sorry pete my error, got over 900 lines & lost myself while checking the
integrity of the results. it's the charge at the higher rate that isn't
working correctly everything is charging at the lower rate even when there is
a "Y" in column C.

=IF(C898="Y",B898*0.05,IF(A8980,B898*0.025," "))
col A Col B Col C Col D
1 5 Y £0.13 should be £0.25?
0 4 Y (blank) should be £0.20?
22 50 (blank) £1.25 correct

just a sample from the sheet. Will this help or is there can i send a copy
of the sheet to you if that would be easier?




"Pete_UK" wrote:

Just check the second part of the formula, i.e.:

IF(A20,B2*0.025,"")

This relates to C2 being blank (as you said it can only contain a "Y"
or a blank) and it only multiplies B2 by 0.025 if A2 is greater than
zero - did you perhaps type < instead of ?

Failing that, post your formula here.

Pete


On Jul 7, 5:41 pm, mahone wrote:
the first part worked charging at the higher rate if there was a "Y" in
column C just the last bit didn't work. it charged at the lower rate when
column C was blank & there was a zero in column A instead of any quantity
greater than zero. any suggestions?



"Pete_UK" wrote:
You're welcome.

Pete

On Jul 6, 10:29 am, mahone wrote:
thanks i'll give this a go when i get back to work next week. hopefully it'll
work & i'll learnt something else.- Hide quoted text -

- Show quoted text -

You can send the file to me he

pashurst at auditel dot net

change the obvious.

Pete

"mahone" wrote in message
...
sorry pete my error, got over 900 lines & lost myself while checking the
integrity of the results. it's the charge at the higher rate that isn't
working correctly everything is charging at the lower rate even when there
is
a "Y" in column C.

=IF(C898="Y",B898*0.05,IF(A8980,B898*0.025," "))
col A Col B Col C Col D
1 5 Y £0.13 should be £0.25?
0 4 Y (blank) should be £0.20?
22 50 (blank) £1.25 correct

just a sample from the sheet. Will this help or is there can i send a copy
of the sheet to you if that would be easier?




"Pete_UK" wrote:

Just check the second part of the formula, i.e.:

IF(A20,B2*0.025,"")

This relates to C2 being blank (as you said it can only contain a "Y"
or a blank) and it only multiplies B2 by 0.025 if A2 is greater than
zero - did you perhaps type < instead of ?

Failing that, post your formula here.

Pete


On Jul 7, 5:41 pm, mahone wrote:
the first part worked charging at the higher rate if there was a "Y" in
column C just the last bit didn't work. it charged at the lower rate
when
column C was blank & there was a zero in column A instead of any
quantity
greater than zero. any suggestions?



"Pete_UK" wrote:
You're welcome.

Pete

On Jul 6, 10:29 am, mahone wrote:
thanks i'll give this a go when i get back to work next week.
hopefully it'll
work & i'll learnt something else.- Hide quoted text -

- Show quoted text -

ok cheers

"Pete_UK" wrote:

You can send the file to me he

pashurst at auditel dot net

change the obvious.

Pete

"mahone" wrote in message
...
sorry pete my error, got over 900 lines & lost myself while checking the
integrity of the results. it's the charge at the higher rate that isn't
working correctly everything is charging at the lower rate even when there
is
a "Y" in column C.

=IF(C898="Y",B898*0.05,IF(A8980,B898*0.025," "))
col A Col B Col C Col D
1 5 Y £0.13 should be £0.25?
0 4 Y (blank) should be £0.20?
22 50 (blank) £1.25 correct

just a sample from the sheet. Will this help or is there can i send a copy
of the sheet to you if that would be easier?




"Pete_UK" wrote:

Just check the second part of the formula, i.e.:

IF(A20,B2*0.025,"")

This relates to C2 being blank (as you said it can only contain a "Y"
or a blank) and it only multiplies B2 by 0.025 if A2 is greater than
zero - did you perhaps type < instead of ?

Failing that, post your formula here.

Pete


On Jul 7, 5:41 pm, mahone wrote:
the first part worked charging at the higher rate if there was a "Y" in
column C just the last bit didn't work. it charged at the lower rate
when
column C was blank & there was a zero in column A instead of any
quantity
greater than zero. any suggestions?



"Pete_UK" wrote:
You're welcome.

Pete

On Jul 6, 10:29 am, mahone wrote:
thanks i'll give this a go when i get back to work next week.
hopefully it'll
work & i'll learnt something else.- Hide quoted text -

- Show quoted text -

Follow up: I received the file, and it turned out that the Y entries in
column C were in fact "Y ", i.e. Y<space. Using CTRL-H on the column
enabled these to be changed to just Y, and then the formula worked.

Pete

"Pete_UK" wrote in message
...
You can send the file to me he

pashurst at auditel dot net

change the obvious.

Pete

"mahone" wrote in message
...
sorry pete my error, got over 900 lines & lost myself while checking the
integrity of the results. it's the charge at the higher rate that isn't
working correctly everything is charging at the lower rate even when
there is
a "Y" in column C.

=IF(C898="Y",B898*0.05,IF(A8980,B898*0.025," "))
col A Col B Col C Col D
1 5 Y £0.13 should be £0.25?
0 4 Y (blank) should be £0.20?
22 50 (blank) £1.25 correct

just a sample from the sheet. Will this help or is there can i send a
copy
of the sheet to you if that would be easier?




"Pete_UK" wrote:

Just check the second part of the formula, i.e.:

IF(A20,B2*0.025,"")

This relates to C2 being blank (as you said it can only contain a "Y"
or a blank) and it only multiplies B2 by 0.025 if A2 is greater than
zero - did you perhaps type < instead of ?

Failing that, post your formula here.

Pete


On Jul 7, 5:41 pm, mahone wrote:
the first part worked charging at the higher rate if there was a "Y"
in
column C just the last bit didn't work. it charged at the lower rate
when
column C was blank & there was a zero in column A instead of any
quantity
greater than zero. any suggestions?



"Pete_UK" wrote:
You're welcome.

Pete

On Jul 6, 10:29 am, mahone wrote:
thanks i'll give this a go when i get back to work next week.
hopefully it'll
work & i'll learnt something else.- Hide quoted text -

- Show quoted text -