O
Old Dad
I am trying to convert from Lotus 1-2-3 to Excel and am having troubl
writing a logic formula to do what I want.
I pay employees of my retail business a bonus based on his or her tota
amount of sales for the month and the average of his or her sales fo
the month (total amount of sales divided by number of sales). To wit:
$50.
$100.
Any sales amount below $5,500 or an average of less than $38 means n
bonus.
In Lotus 1-2-3 I can nest IF statements to three levels with th
following formula
@IF(A1>=5500#AND#B1>=38,@IF(A1>=8500#AND#B1>=47,100,@IF(A1<8500#AND#B1<47,25,50)),0
In Excel 2000, however, I seem to be only to nest two levels. Th
Formula Palette only goes to two levels (an then returns only TRUE o
FALSE), and if I try to nest more than two by writing the formul
myself I get an error message that says “too many arguments for thi
function”. I am trying to do something similar to this:
=IF((AND(A1>=5500, B1>=38),AND(A1>=8500, B1>=47),100,AND(A1<8500
B1<47),25,50),0)
I find it hard to believe Lotus 1-2-3 has a capability Excel doesn’t.
Do I need a newer version of Excel? Can anyone provide any help o
guidance on this?
Thanks,
Old Da
writing a logic formula to do what I want.
I pay employees of my retail business a bonus based on his or her tota
amount of sales for the month and the average of his or her sales fo
the month (total amount of sales divided by number of sales). To wit:
more the bonus is $25. If average sales are $47 or more the bonus iIf he or she sells $5,500 or more and the average sale is $38 o
$50.
more the bonus is $50. If average sales are $47 or more the bonus iIf he or she sells $8,500 or more and the average sale is $38 o
$100.
Any sales amount below $5,500 or an average of less than $38 means n
bonus.
In Lotus 1-2-3 I can nest IF statements to three levels with th
following formula
@IF(A1>=5500#AND#B1>=38,@IF(A1>=8500#AND#B1>=47,100,@IF(A1<8500#AND#B1<47,25,50)),0
In Excel 2000, however, I seem to be only to nest two levels. Th
Formula Palette only goes to two levels (an then returns only TRUE o
FALSE), and if I try to nest more than two by writing the formul
myself I get an error message that says “too many arguments for thi
function”. I am trying to do something similar to this:
=IF((AND(A1>=5500, B1>=38),AND(A1>=8500, B1>=47),100,AND(A1<8500
B1<47),25,50),0)
I find it hard to believe Lotus 1-2-3 has a capability Excel doesn’t.
Do I need a newer version of Excel? Can anyone provide any help o
guidance on this?
Thanks,
Old Da