"if" function

[JOSIL]

Why the following excel formula keep giving me an error?

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

 

[Pete_UK]

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

 

[Guest]

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.

 

[daddylonglegs]

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 require

 

[JOSIL]

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.

 

[Pete_UK]

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

 

[Guest]

try something like

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

Stu

 

[daddylonglegs]

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

=INDEX(J29:Z29,I26)*G

 

[JOSIL]

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%

 

[daddylonglegs]

Then amend my formula to

=INDEX(J29:O29,MIN(6,I26))*G

 

[JOSIL]

My friends


I found the reason that gives errors.
My excell is in Portuguese and the statement must be like this:

=SE(K26=1;$G6*$J29;SE(K26=2;$G6*K29;SE(K26=3;$G6*L29))*0)

SE mean IF and have to use *; *instead of *,*

Just remember this for future.

Thanks a lot for everybody

 

[daddylonglegs]

Hi Josil,

Try

=ÍNDICE(J29:O29;MÍNIMO(6;I26))*G6