Formula based on response of another formula

[MilenaKanwal]

I have a spreadsheet that pulls date from another file to populate all but 1 column. In that column I need to have a formula that returns one of three responses:

If E2 is blank, then keep F2 blank
If E2 has a 1, then F2 should say 'single'
If E2 has a value greater than 1, then F2 should say 'multiple'

E2's formula begins with an IFERROR function, so if there is no data in the corresponding cell of the source data, it leaves the cell as a blank. ("")

I tried 2 different nested IF functions (see below), but somehow I can't seem to think of how make it all come together.

=IF(E2=1,"Single",IF(E2="","","Multiple"))
=IF(E2="","",IF(E2=1,"Single",IF(E2>1,"Multiple","")))

 

[Jonmo1]

Either of those formulas should do what you're describing.

Can you post the formula that you have in E2 ?
Perhaps it's not returning a real number, but a number stored as text.

Also please describe in more detail what happens when you tried those formulas..
Did it give an error? What error?
Did you get the wrong result, what result DID you get, what result did you expect, and why?

 

[MilenaKanwal]

The formula in E2 is:

=ABS(IFERROR(INDIRECT("'SHEET1'!"&ADDRESS(MATCH($A138,sheet1!$A$1:$A$3000,0),MATCH(I$1, sheet1!$2:$2,0))),""))

Results are as follows:

=IF(E2=1,"Single",IF(E2="","","Multiple"))

Returns a blank if blank, 'Multiple' if greater than 1 and 'Multiple' if '1'

------------------------------------------------------------------------------

=IF(E2="","",IF(E2=1,"Single",IF(E2>1,"Multiple","")))

Returns a blank if blank, 'Multiple' if greater than 1 and blank if '1'

Expected returns:

If E2 is blank, then keep F2 blank
If E2 has a 1, then F2 should say 'single'
If E2 has a value greater than 1, then F2 should say 'multiple'

Either of those formulas should do what you're describing.

Can you post the formula that you have in E2 ?
Perhaps it's not returning a real number, but a number stored as text.

Also please describe in more detail what happens when you tried those formulas..
Did it give an error? What error?
Did you get the wrong result, what result DID you get, what result did you expect, and why?

 

[MilenaKanwal]

I figured out my problem!

I should have had the formula in E2 begin with ROUND to account for source data that was less than 1 but more than 0 (ie .78)

Once I did that, it worked perfectly!

 

[Jonmo1]

Again, either of those 2 formulas will do what you're asking.

It sounds like E2 is NOT exactly 1.
Perhaps it's actually 1.05 or something, with the cell formatted to 0 decimals.
So it appears rounded to 1, but it's not really exactly 1.

Format E2 to show 15 decimals, what do you see?