Please help with if!

[jay7227021]

if anyone can help me I would greatly appreciate it. Im making a payroll calculator on my excel to check my work's calculation because they always seem a little off. I cant figure out this one cell' if function. ill try to explain it the best i can with my versian of the spreadsheet here


n=any number

b1=n
b2=n

b4= needs to be "(if (b1<40, keep it b1 but if b1>40, keep it 40)+(if"b2<40, keep it b2, but if b2>40, keep it 40)

basically i need b4 to add the two up using the real numbers if theyre less than 40 or using only 40 is its greater than 40 for each cell.

anyone? thank you so much.

[JieJenn]

Try this.

=IF(B1<40,B1,IF(B1>40,40,""))+IF(B2<40,B2,IF(B2>40,40,""))

PS: What happen if B1 or B2 = 40?

[Woozer74]

Hi

Try:

=IF(B1<40,B1,40)+IF(B2<40,B2,40)

Does that make sense?

[jay7227021]

Ill try both when i get home. If b1 and/or b2 =40 then it should use the 40

[jay7227021]

JieJenn, thank you so much! it helped perfectly! i really do appreciate it. ive been trying to figure that out all day.

[jay7227021]

AAHHHHH...one more thing. JieJenn, your formula worked for it the numbers were above and below 40, but if the numbers are 40 for both cells, the c4=True and i need it to enter a number.

[jay7227021]

Actually, Woozer, yours worked perfectly. thank you!

[tigeravatar]

Alternate formula:
=MIN(B1,40)+MIN(B2,40)

[jay7227021]

Solved thanks everyone!