formula help

[mahone]

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.

 

[Pete_UK]

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

=IF(C2="Y",B2*0.05,IF(A2>0,B2*0.025,""))

Hope this helps.

Pete

 

[mahone]

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]

You're welcome.

Pete

 

[mahone]

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]

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

IF(A2>0,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

 

[mahone]

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(A898>0,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]

You can send the file to me here:

pashurst at auditel dot net

change the obvious.

Pete

 

[mahone]

ok cheers

You can send the file to me here:

pashurst at auditel dot net

change the obvious.

Pete

 

[Pete_UK]

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