SOLVED

Formula assistance (IF Statment)

%3CLINGO-SUB%20id%3D%22lingo-sub-1252322%22%20slang%3D%22en-US%22%3EFormula%20assistance%20(IF%20Statment)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1252322%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%203%20columns%3A%20Column%20A%20has%20numeric%20values%20Column%20B%20has%20numeric%20values%20and%20empty%20cells.%26nbsp%3B%20Column%20C%20is%20there%20to%20calc%20Columns%20A%20and%20B.%26nbsp%3B%20I'm%20able%20to%20return%20a%20value%20between%20Columns%20A%20and%20B%2C%20if%20numeric%20values%20are%20present.%26nbsp%3B%20I'd%20like%20to%20turn%200%20or%20nothing%20in%20Column%20C%2C%20if%20Column%20B%20is%20empty.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1252322%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1252368%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20assistance%20(IF%20Statment)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1252368%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20you%20show%20us%20your%20formula%20or%20a%20file%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1252378%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20assistance%20(IF%20Statment)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1252378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F594564%22%20target%3D%22_blank%22%3E%40toyawallace%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20adopt%20this%20approach...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(%3CSTRONG%3EB2%3D%22%22%2C%22%22%2C%3C%2FSTRONG%3E%3CEM%3E%3CYOUR%20calculation%3D%22%22%20here%3D%22%22%20if%3D%22%22%20column%3D%22%22%20b%3D%22%22%20is%3D%22%22%20not%3D%22%22%20empty%3D%22%22%3E%3C%2FYOUR%3E%3C%2FEM%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlace%20the%20above%20formula%20in%20C2%20and%20replace%20%3CYOUR%20calculation%3D%22%22%20here%3D%22%22%20if%3D%22%22%20column%3D%22%22%20b%3D%22%22%20is%3D%22%22%20not%3D%22%22%20empty%3D%22%22%3E%20part%20with%20actual%20calculation%20you%20are%20doing%20if%20column%20B%20is%20not%20empty.%3C%2FYOUR%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1252392%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20assistance%20(IF%20Statment)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1252392%22%20slang%3D%22en-US%22%3Ethanks%2C%20here's%20the%20formula%3A%20%3DIF(O16%26gt%3B%3D0%2CO16-N16)%2BIF(O16%3D%22%22%2C0)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1252395%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20assistance%20(IF%20Statment)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1252395%22%20slang%3D%22en-US%22%3EThanks%2C%20here's%20the%20calculation%3A%20%3DIF(O16%26gt%3B%3D0%2CO16-N16)%2BIF(O16%3D%22%22%2C0)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1252409%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20assistance%20(IF%20Statment)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1252409%22%20slang%3D%22en-US%22%3E%3DIF(O16%26gt%3B%3D0%2CO16-N16)%2BIF(O16%3D%22%22%2C0)%20Thanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1252481%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20assistance%20(IF%20Statment)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1252481%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1252464%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20assistance%20(IF%20Statment)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1252464%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F594564%22%20target%3D%22_blank%22%3E%40toyawallace%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I have 3 columns: Column A has numeric values Column B has numeric values and empty cells.  Column C is there to calc Columns A and B.  I'm able to return a value between Columns A and B, if numeric values are present.  I'd like to turn 0 or nothing in Column C, if Column B is empty.

 

18 Replies
Highlighted
Hello,

Can you show us your formula or a file?
Highlighted

@toyawallace 

You may adopt this approach...

 

=IF(B2="","",<your calculation here if column B is not empty>)

 

Place the above formula in C2 and replace <your calculation here if column B is not empty> part with actual calculation you are doing if column B is not empty.

Highlighted
thanks, here's the formula: =IF(O16>=0,O16-N16)+IF(O16="",0)
Highlighted
Thanks, here's the calculation: =IF(O16>=0,O16-N16)+IF(O16="",0)
Highlighted
=IF(O16>=0,O16-N16)+IF(O16="",0) Thanks
Highlighted
Highlighted
Highlighted

@Subodh_Tiwari_sktneer 

Trying to reflect the following in column U:

If N is >= to 0, then N-O. 

If N has no value, then column U should be 0.

Highlighted

@toyawallace  would this work?

iferror( columnB - column A, 0)

Highlighted

@mtarler 

Hi, here's the formula to reflect to return a value if N>=0, and return 0, if there is no value in N.

=IF(O16>=0,O16-N16,IFERROR(O16-N16,0))

Highlighted

@toyawallace 

Did you upload a wrong file as there is no data in the columns you described?

 

In the file you uploaded, the cells in column B which look empty but actually they contain a space in them. Why?

If you select all the cells in column B which look empty and press Delete from the keyboard, you simply need the following formula...

 

In H8

 

 

 

=IF(B8>=0,B8-A8,0)

 

 

 

and copy it down.

 

 

Highlighted
=IF(O16>=0,O16-N1,0)

Highlighted

I apologize, I was providing an example in my first post.  In the actual file. I need the value to populate in Column U.  The columns to include the formula are O and N.  If the value in N is blank or no value at all, I'd like for U to be 0.  If the value in N>=0, then the value in U should be O-N.

Highlighted

Thanks, it's still returning a #Value

 

Highlighted

@toyawallace  not sure why it isn't working if I understand what you want:

=iferror(O16-N16,0)    

if you only want calculated if N>=0 then

=iferror(if(N>=0,O16-N16,0),0)

 

Highlighted

@toyawallace 

You still have no data in columns you described in the latest file you uploaded.

Anyways, try one of the following formula depending which column actually contain the data.

=IF(ISNUMBER(B16),IF(B16>=0,B16-A16,0),0)

OR

=IF(ISNUMBER(N16),IF(N16>=0,O16-N16,0),0)

OR

=IF(ISNUMBER(B8),B8-A8,0)

OR

=IF(ISNUMBER(N16),O16-N16,0)

See which formula returns the desired output.

Highlighted

@Subodh_Tiwari_sktneer 

Hi there, you nailed it!  

This formula worked.  =IF(ISNUMBER(O16),IF(O16>=0,O16-N16,0),0)

Thank you so very much!!  

Stay healthy and Be Safe!

Highlighted
Best Response confirmed by toyawallace (Occasional Contributor)
Solution

@toyawallace 

You're welcome! Glad it worked as desired.

 

Please take a minute to accept the post with the provided solution as a Best Response/Official Answer to mark your question as Solved.

 

Stay home and be safe.