Forumla Help

[Guest]

Hi All,

I am putting together a formula that will help me determine if someone is
paid weekly or bi-weekly. I currently have a payroll codes in Column A and
then in Columns M & O I have the codes broken up into weekly and biweekly. I
am also trying to link a salary amount which is in column G.

Here is the formula I have already put together but I need some help.

=IF(A6=$M$2:$M$5,G6/12/100*0.14*12/52,IF(A6=$O$2:$O$4,G6/12/100*0.14*12/52*2))

Any and all assistance would be greatly welcomed.

Here is a little snippet of my spreadsheet.

Thanks.
Adam

A G J M
O
Co Code Salary Answer Co Code (weekly) Co Code
(bi-weekly)
BE5 19760 BE5

 

[Sandy Mann]

I don't really understand what it is that you are doing or what it is you
are asking but looking at your formula a couple of things stand out.

If the formula is array entered, (Ctrl + Shift + Enter), the A6=$M$2:$M$5
will return an array of TRUE/FALSE's and the formula will only use the first
TRUE/FALSE (if not array entered I get a #VALUE! error). If you mean if the
value in A6 is also anywhere in the range M2:M5 then if it would be better
to use something likeCOUNTIF($M$2:$M$5,A6) to return a number or zero. (not
that zero is not a number but IF uses zero to mean FALSE).

Your formula will then become:

=IF(COUNTIF($M$2:$M$5,A6),G6/12/100*0.14*12/52,IF(COUNTIF($O$2:$O$5,A6),G6/12/100*0.14*12/52*2),"")
(I assume that $O$2:$O$4 is a typo for $O$2:$O$5)

I also added "" as a FALSE argument otherwise you will get FALSE returned
when there is no match in M or O


--
HTH

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