SOLVED

IFS Function

%3CLINGO-SUB%20id%3D%22lingo-sub-2117284%22%20slang%3D%22en-US%22%3EIFS%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117284%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Cleriksiphiwo_1-1612753203067.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253153i86BE0D92B522B0AD%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Cleriksiphiwo_1-1612753203067.png%22%20alt%3D%22I%20am%20using%20IFS%20Function%20in%20C13.%20Now%2C%20the%20problem%20the%20formula%20returns%200%20as%20you%20can%20see%20in%20the%20table%20if%20the%20%26quot%3BCollected%20By%26quot%3B%20column%20is%20empty.%20How%20can%20I%20make%20the%20function%20to%20return%20nothing%20if%20the%20%26quot%3BCollected%20By%26quot%3B%20is%20empty%3F%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EI%20am%20using%20IFS%20Function%20in%20C13.%20Now%2C%20the%20problem%20the%20formula%20returns%200%20as%20you%20can%20see%20in%20the%20table%20if%20the%20%22Collected%20By%22%20column%20is%20empty.%20How%20can%20I%20make%20the%20function%20to%20return%20nothing%20if%20the%20%22Collected%20By%22%20is%20empty%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2117284%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121349%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121349%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20Thank%20you%20so%20much%20Wyn%20for%20your%20help!%3C%2FP%3E%3CP%3ENow%2C%20the%20problem%20I%20am%20facing%20is%20that%20I%20do%20not%20have%20'Empty%20list'%20in%20Custom%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2117425%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117425%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F958761%22%20target%3D%22_blank%22%3E%40Cleriksiphiwo%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20it's%20returning%20text%2C%20then%20you%20could%20wrap%20your%20entire%20formula%20in%20T(%20)%26nbsp%3B%20%26nbsp%3Bthat%20will%20return%20a%20blank%20instead%20of%20a%20zero.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorWyn%20Hopkins_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3EAlternatively%20you%20could%20just%20format%20your%20cell%20to%20show%20blanks%20for%20zeros%26nbsp%3B%20(although%20I'd%20recommend%20putting%20a%20small%20dash%20or%20something%20just%20to%20show%20there's%20nothing%20there%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20508px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253191iB15CFFFBAD709B17%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20678px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253192iB59A8F234AC67483%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWyn%3C%2FP%3E%0A%3CP%3EMVP%3C%2FP%3E%0A%3CP%3EUTC%2B8%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2117340%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117340%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F958761%22%20target%3D%22_blank%22%3E%40Cleriksiphiwo%3C%2FA%3E%26nbsp%3BWould%20be%20helpful%20if%20you%20could%20show%20the%20formula%20that%20returns%20the%20zero.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2125322%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2125322%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3E%3CBR%20%2F%3ENot%20sure%20what%20you%20mean%20sorry.%20You%20can%20type%20in%20your%20own%200.00%3B(0.00)%3B%22Empty%20List%22%20into%20the%20Type%20box%20%3CBR%20%2F%3E%3CBR%20%2F%3E(side%20note%2C%20you%20seem%20to%20have%20marked%20your%20own%20reply%20as%20best%20solution%20%3Agrinning_face%3A%20)%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2125608%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2125608%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20noticed%20that%2C%20too!%20Perhaps%2C%20%3CEM%3Emarking%20one's%20own%20reply%20as%20%22%3CSTRONG%3Ebest%20response%3C%2FSTRONG%3E%22%3C%2FEM%3E%20must%20be%20%3CSTRONG%3Edisabled%3C%2FSTRONG%3E.%20In%20the%20academe%2C%20such%20is%20%3CSTRONG%3Eanalogous%20to%20checking%20your%20own%20paper%20and%20determining%20your%20own%20rating%2C%20based%20on%20your%20own%20standards%3C%2FSTRONG%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2125899%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2125899%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20so%20sorry%20for%20that%20guys%2C%20I%20meant%20to%20mark%20yours.%20Please%20accept%20my%20apology%2C%20it%20was%20a%20genuine%20mistake%20and%20I%20will%20correct%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

Hi,

 

I am using IFS Function in C13. Now, the problem the formula returns 0 as you can see in the table if the "Collected By" column is empty. How can I make the function to return nothing if the "Collected By" is empty?I am using IFS Function in C13. Now, the problem the formula returns 0 as you can see in the table if the "Collected By" column is empty. How can I make the function to return nothing if the "Collected By" is empty?

 

 

7 Replies

@Deleted Would be helpful if you could show the formula that returns the zero.

Best Response
Solution

@Deleted

 

If it's returning text, then you could wrap your entire formula in T( )   that will return a blank instead of a zero.

 

 

Alternatively you could just format your cell to show blanks for zeros  (although I'd recommend putting a small dash or something just to show there's nothing there

 

image.png

 

 

or

 

image.png

 

Wyn

MVP

UTC+8

@Wyn Hopkins 

 

Hi, Thank you so much Wyn for your help!

Now, the problem I am facing is that I do not have 'Empty list' in Custom

Hi

Not sure what you mean sorry. You can type in your own 0.00;(0.00);"Empty List" into the Type box

(side note, you seem to have marked your own reply as best solution )

@Wyn Hopkins 

I noticed that, too! Perhaps, marking one's own reply as "best response" must be disabled. In the academe, such is analogous to checking your own paper and determining your own rating, based on your own standards.

@Twifoo 

 

I am so sorry for that guys, I meant to mark yours. Please accept my apology, it was a genuine mistake and I will correct it.

No worries