Nested IF

Jeana

This seems like a pretty simple nested IF statement but it's not ever hitting the last condition. Any insight would be helpful.

I'm autogenerating an Asset #. I want the formula to look at the Asset Type and if it's a "Video" insert a "V" and the autogenerated number (this works), if there is something in the Suite cell, put that and the autogenerated number, If not put what's in State and the autogenerated number.

What am I missing?

=IF([Asset Type]@row = "Video", "V" + [Auto-Number]@row, IF(Suite@row <> " ", Suite@row + [Auto-Number]@row, (State@row + [Auto-Number]@row)))

Thanks!

Kelly Moore

No, I wanted to make sure we didn't need to add text to it to force it to text.

I deleted the formula from the 2nd IF and rebuilt it again. Perhaps there was an inadvertent space we were not seeing but the formula is now working on my test sheet

=IF([Asset Type]@row = "Video", "V" + [Auto-Number]@row, IF(Suite@row <> "", Suite@row + [Auto-Number]@row, State@row + [Auto-Number]@row))

Kelly Moore

Hey @Jeana

The formula above has a leading parenthesis prematurely closing off your last term. Try this

=IF([Asset Type]@row = "Video", "V" + [Auto-Number]@row, IF(Suite@row <> " ", Suite@row + [Auto-Number]@row, State@row + [Auto-Number]@row))

Does that work for you?

Kelly

Jeana

Appreciate the response Kelly but that didn't work. When the last condition is met I still only get the auto generated number and not the state code in front of it. The other conditions work fine in testing.

Any other thoughts?

Jeana

Kelly Moore

Hey Jeana

Is your state code a text string, eg. NY, or is it a number?

Jeana

It's a value (NV), good point. Do I need a VALUE statement in here? The Suite is also a Value and it's working.

Kelly Moore

No, I wanted to make sure we didn't need to add text to it to force it to text.

I deleted the formula from the 2nd IF and rebuilt it again. Perhaps there was an inadvertent space we were not seeing but the formula is now working on my test sheet

=IF([Asset Type]@row = "Video", "V" + [Auto-Number]@row, IF(Suite@row <> "", Suite@row + [Auto-Number]@row, State@row + [Auto-Number]@row))