Why the following excel formula keep giving me an error?

=IF (I26 = 1, THEN (G6*J29))


--
JOSIL
------------------------------------------------------------------------
JOSIL's Profile: http://www.excelforum.com/member.php...o&userid=37175
View this thread: http://www.excelforum.com/showthread...hreadid=568918

Hi

Try this:
=IF(I26=2,G6*J29,"")

The format for an IF formula is:
=IF(criteria , answer if TRUE , answer if FALSE)

Hope this helps.
Andy.

"JOSIL" wrote in
message ...

Why the following excel formula keep giving me an error?

=IF (I26 = 1, THEN (G6*J29))


--
JOSIL
------------------------------------------------------------------------
JOSIL's Profile:
http://www.excelforum.com/member.php...o&userid=37175
View this thread: http://www.excelforum.com/showthread...hreadid=568918

You do not need the "THEN" - amend as follows:

=IF (I26 = 1, G6*J29 )

You have not specified what you want to happen if I26 is not equal to
1.

Hope this helps.

Pete

JOSIL wrote:
Why the following excel formula keep giving me an error?

=IF (I26 = 1, THEN (G6*J29))


--
JOSIL
------------------------------------------------------------------------
JOSIL's Profile: http://www.excelforum.com/member.php...o&userid=37175
View this thread: http://www.excelforum.com/showthread...hreadid=568918

Ok let me explain what I'm doing

If a person makes 1 sale, then earns 5% comission, therefore receives
5% of the amount the company has received.

If makes 2 sales then earns 7%, and so on

so, if 1 sale (I26=1) then receives amount (g6) times 5% (j29);

if 2 sales (I26=2) then receives amount (g6) times 7% (k29);

and so on

That helps?

I've tried all the tips already but does not working.


--
JOSIL
------------------------------------------------------------------------
JOSIL's Profile: http://www.excelforum.com/member.php...o&userid=37175
View this thread: http://www.excelforum.com/showthread...hreadid=568918

How many different criteria do you have? If it is only a few then you
could use a nested IF function, such as:

=IF(I26=1,G6*J29,IF(I26=2,G6*K29,IF(I26=3,G6*L29,0 ))) etc

If you have several more conditions then it would be better to use some
kind of LOOKUP formula, where you can get the %age values from a table,
as you can only have up to 7 nested IFs.

Hope this helps.

Pete

JOSIL wrote:
Ok let me explain what I'm doing

If a person makes 1 sale, then earns 5% comission, therefore receives
5% of the amount the company has received.

If makes 2 sales then earns 7%, and so on

so, if 1 sale (I26=1) then receives amount (g6) times 5% (j29);

if 2 sales (I26=2) then receives amount (g6) times 7% (k29);

and so on

That helps?

I've tried all the tips already but does not working.


--
JOSIL
------------------------------------------------------------------------
JOSIL's Profile: http://www.excelforum.com/member.php...o&userid=37175
View this thread: http://www.excelforum.com/showthread...hreadid=568918

try something like

=if(I26=1,G6*J29,if(I26=2,g6*K29,if(I26=3,g6*L29)) ) and so on.

Stu


"JOSIL" wrote:


Ok let me explain what I'm doing

If a person makes 1 sale, then earns 5% comission, therefore receives
5% of the amount the company has received.

If makes 2 sales then earns 7%, and so on

so, if 1 sale (I26=1) then receives amount (g6) times 5% (j29);

if 2 sales (I26=2) then receives amount (g6) times 7% (k29);

and so on

That helps?

I've tried all the tips already but does not working.


--
JOSIL
------------------------------------------------------------------------
JOSIL's Profile: http://www.excelforum.com/member.php...o&userid=37175
View this thread: http://www.excelforum.com/showthread...hreadid=568918

You could use INDEX, depending on the number of options you have

=INDEX(J29:Z29,I26)*G6


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=568918

Nop.

My dear friends. None of the three options is working.


I only have 6 variables: 1=5%, 2=7%, 3=9% 4=12% 5=15% 6 and more=20%


--
JOSIL
------------------------------------------------------------------------
JOSIL's Profile: http://www.excelforum.com/member.php...o&userid=37175
View this thread: http://www.excelforum.com/showthread...hreadid=568918

THEN is not a recognised worksheet function. Try

=IF(I26=1,G6*J29,"")

If I26 is not equal to 1 this will give a blank, change if required


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=568918