IF Formula - nested

[BellaShore]

Hi,
I need to create an IF formula based the following information (my goal is to have a column with hourly rate per employee, whether they are hourly or salary so I can do a vacation accrual - see details below):

col BC this column says either SALARY of HOURLY on each line (by employee)
col BD this column has an annual salary amount or an hourly $ amount by employee - depending on which they are in colummBC

I need to add another column with an IF function that will return either the salary amount in COL BD divided by 1827 hours (to get an hourly rate), if column BC says salary, or if they are HOURLY in col BC, then the rate in COLUMN BD

The formula I tried is not working: =IF(BC2=SALARY,BD2/1827,IF(BC2=HOURLY,BD2))

Sounds pretty basic and I am sitting here with an Excel Function book - but thought someone might be able to help.
Bella

 

[Aryatect]

Hi, Welcome to the Message Board

Try

=IF(BC2="SALARY",BD2/1827,IF(BC2="HOURLY",BD2,""))

 

[BellaShore]

Thanks so much. This worked great for the salary lines, but returned nothing for the hourly lines.

[TABLE="width: 182"]
<colgroup><col width="64" style="width: 48pt;"> <col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Pay Code[/TD]
[TD="width: 114, bgcolor: transparent"]Pay Rate Amount[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SALARY[/TD]
[TD="bgcolor: transparent, align: right"]59100.49[/TD]
[TD="bgcolor: transparent"] $ 32.35 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]HOUR[/TD]
[TD="bgcolor: transparent, align: right"]11.2[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SALARY[/TD]
[TD="bgcolor: transparent, align: right"]95000.1[/TD]
[TD="bgcolor: transparent"] $ 52.00 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SALARY[/TD]
[TD="bgcolor: transparent, align: right"]42000.14[/TD]
[TD="bgcolor: transparent"] $ 22.99 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SALARY[/TD]
[TD="bgcolor: transparent, align: right"]84472.15[/TD]
[TD="bgcolor: transparent"] $ 46.24 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]HOUR[/TD]
[TD="bgcolor: transparent, align: right"]12.75[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]HOUR[/TD]
[TD="bgcolor: transparent, align: right"]17.02[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]HOUR[/TD]
[TD="bgcolor: transparent, align: right"]12.75[/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[BellaShore]

Hi, Welcome to the Message Board

Try

=IF(BC2="SALARY",BD2/1827,IF(BC2="HOURLY",BD2,""))

Thank you very much. This worked well for the Salary lines but did not return anything for the hourly lines.

 

[Aryatect]

The formula you put there was fro HOURLY that's why it is not returning anyvalue. If you are using HOUR then use below:

=IF(BC2="SALARY",BD2/1827,IF(BC2="HOUR",BD2,""))

 

[Aryatect]

Just in case if it can be both HOUR or HOURLY or PER HOUR like that then use below:

=IF(BC2="SALARY",BD2/1827,SUMIF(BC2,"*HOUR*",BD2))

 

[BellaShore]

Ahhhh...yes. Thank you so much!!!!! I really appreciate your help!!!!!!!~

 

[Aryatect]

Glad could help and thanks for the feedback!