Nested IF function error

Copper Contributor

I am a Business student taking a class to refresh my knowledge of spreadsheets and using Excel.  My school uses Office 365.  The function I have is this:

 

=IF(G18>10,$G$14,"=IF(G18>5,$G$13,$G$14)")

 

When I copy it down the column, the row reference in the second IF function does not change but the first one does.  One row down from G18 looks like this:

 

=IF(G19>10,$G$14,"=IF($G18>5,$G$13,$G$14)")

 

I cannot for the life of me figure out what I am doing wrong.  The first function returns the correct result but when I copy it, only the first conditional changes in the first IF function.  

 

Any help/direction/advice?  

 

 

3 Replies

Hi,

 

You have no second IF in your formula. You have only one IF which returns for FALSE condition returns some text. You have the word "IF" within that text, together with other text, but that's not a formula IF.

Seemingly, this is the formula you need:
=IF(AND(G18<=10,G18>5),
G13,
G14)

Hi @rmunday 

 

You nearly had it. It would look like =IF(G18>10, $G$14, IF(G18>5, $G$13, $G$14))

 

You don't need to add in " " as it will make it text as Sergei has said, and you don't need to use = when nesting fucntions.

 

EDIT: What Twifoo has put is also correct but does not need nesting.

 

One a side note, this forum is a great place to learn about Excel from people like @Sergei Baklan and our other experts. Well worth a browse and check in every now and again!

 

Cheers

Damien