Hello,

I would like to know how to remove the "False" Value when if function is nested.

Here is an Example

=IF(C11="Evening",200,IF(C11="Gazetted",1000,IF(C11="Night",200)))

Now When i select the value link to this Function i get the desired result as i wanted , but now i dont want the

False Value to be shown d if the given criteria in the cell is not Met

I tried this also

=IFERROR(IF(C9="Evening",200,IF(C9="Gazetted",1000,IF(C9="Night",200))),"")

But still i am getting the same False Value in default

Expert advice is much appreciated on this

Thank you

Waiting

Posez votre question
Signaler

# REMOVING " FALSE " VALUE IN "IF&quo [Résolu/Fermé]

JIN - Dernière réponse le Jul 18, 2013 12:01PM

Lire la suite

- REMOVING " FALSE " VALUE IN "IF&quo
- I dont want to diplay "false" when condition is false
- Excel show zero not false
- Need help to remove FALSE from cell (Résolu) » Forum - Office software
- Remove False from cell (Résolu) » Forum - Office software
- False value, other than the weekly total » Forum - Excel
- Excel - Remove #Value error message » How-To - Excel
- Insert/Remove photos depending up on ref. cell value changing (Résolu) » Forum - Excel

Okay, well excep that u have to have semicolons(;) instead of commas (,) in the function it worked pretty fine for me with some corrections. Like this...

this is what u have

=IF(C11="Evening",200,IF(C11="Gazetted",1000,IF(C11="Night",200)))

where it should be

=IF(C11="Evening";200;IF(C11="Gazetted";1000;IF(C11="Night";200;)))

notice the semicolon on the end, if voided u will have an error because u dont have false state

this function will result with a ZERO (0) if false

you can also do this

=IF(C11="Evening";200;IF(C11="Gazetted";1000;IF(C11="Night";200;"")))

this will result with a blank cell if false

i hope it helped

this is what u have

=IF(C11="Evening",200,IF(C11="Gazetted",1000,IF(C11="Night",200)))

where it should be

=IF(C11="Evening";200;IF(C11="Gazetted";1000;IF(C11="Night";200;)))

notice the semicolon on the end, if voided u will have an error because u dont have false state

this function will result with a ZERO (0) if false

you can also do this

=IF(C11="Evening";200;IF(C11="Gazetted";1000;IF(C11="Night";200;"")))

this will result with a blank cell if false

i hope it helped

Probably too late for this, but....

Excel does not use the ; it uses a comma to seperate the parts of the function.

The correct format for what you want to do is:

=IF(D11="Evening",200,IF(D11="Gazetted",1000,IF(D11="Night",200,"")))

this will not make the false value appear if the criteria is not met.

The $ function is used to make cells absolute. So for example, you have a cell B1 with value =A1

if you copy this field down, each row underneath will change to the relative value - ie B2 will change to =A2, B3 will change to = A3, etc

If you put =A$1, when dragging the column down, the value will stay the same. So if cell B1 has value =A$1 and you drag this down for other cells, then the value will stay the same, ie =A$1 for B2, B3, B4 etc

Excel does not use the ; it uses a comma to seperate the parts of the function.

The correct format for what you want to do is:

=IF(D11="Evening",200,IF(D11="Gazetted",1000,IF(D11="Night",200,"")))

this will not make the false value appear if the criteria is not met.

The $ function is used to make cells absolute. So for example, you have a cell B1 with value =A1

if you copy this field down, each row underneath will change to the relative value - ie B2 will change to =A2, B3 will change to = A3, etc

If you put =A$1, when dragging the column down, the value will stay the same. So if cell B1 has value =A$1 and you drag this down for other cells, then the value will stay the same, ie =A$1 for B2, B3, B4 etc

Excel uses the ; sign to define where the conditions of the (for example) IF function starts and ends (same is with all other functions) since excel is kind of a simplified programming language u can think of the ; sign as a carriage return, whereas the , sign is used only to combine references or arrays of values (cells). By defining the IF function by commas you basically just told the excel the argument which you want to be tested for true or false, but you did not state what is the true and false condition result, so the probable result will be an error. check your syntax for properly set ; signs.

the IF function (also nearly any function) in excel works as it works in any programming language

IF(something you need tested for true or false) then TRUE is something FALSE is something else

in excel it works like

IF(A1>A2;B1;B2)

if a1 is larger than a2 then the cell containing the function will result in value of cell b1

else if a1 is not larger than a2 the cell will contain value of cell b2

okay

but

IF(A1>A2,B1,B2)

actually is not a valid function meaning that u test if A1 is larger than the intersection of A2 B1 and B2 which is impossible by logic because A2 B1 and B2 are cells not arrays or references so they can't have intersections that is why u get an error message. and supplemental to that in the above function there is no value for TRUE or FALSE defined so that is not a complete function and therefore can't be executed

The INDEX function is basically useless by itself. not completely useless but is better when used with other functions

is is used for referencing, lets make it complex like

INDEX(a1:f10;if(h5>m5;2;4);if(g1>n1;1;5))

meaning

the cell containing the func will result in a value from the array from a1 to f10, and the result will be if h5 is larger than m5 from the second row in the array if not larger from the fourth row, and if g1 is larger than n1 from the first column and if not larger from the fifth column.

INDEX(array;row;column)

meaning the value will be the intersection of the ROW and COLUMN in the given ARRAY

again, from the ARRAY, not the actual row and column numbers

and for the $ sign, as far as I know it is used for showing US currency nothing else. i'm not familiar with any other usage of it in excel.

the IF function (also nearly any function) in excel works as it works in any programming language

IF(something you need tested for true or false) then TRUE is something FALSE is something else

in excel it works like

IF(A1>A2;B1;B2)

if a1 is larger than a2 then the cell containing the function will result in value of cell b1

else if a1 is not larger than a2 the cell will contain value of cell b2

okay

but

IF(A1>A2,B1,B2)

actually is not a valid function meaning that u test if A1 is larger than the intersection of A2 B1 and B2 which is impossible by logic because A2 B1 and B2 are cells not arrays or references so they can't have intersections that is why u get an error message. and supplemental to that in the above function there is no value for TRUE or FALSE defined so that is not a complete function and therefore can't be executed

The INDEX function is basically useless by itself. not completely useless but is better when used with other functions

is is used for referencing, lets make it complex like

INDEX(a1:f10;if(h5>m5;2;4);if(g1>n1;1;5))

meaning

the cell containing the func will result in a value from the array from a1 to f10, and the result will be if h5 is larger than m5 from the second row in the array if not larger from the fourth row, and if g1 is larger than n1 from the first column and if not larger from the fifth column.

INDEX(array;row;column)

meaning the value will be the intersection of the ROW and COLUMN in the given ARRAY

again, from the ARRAY, not the actual row and column numbers

and for the $ sign, as far as I know it is used for showing US currency nothing else. i'm not familiar with any other usage of it in excel.

**Jackt76**- Jul 12, 2013 10:59PM

Dear All,

semicolons(;) and commas (,) are both correct !!

The difference depends by the Region (Country) of the user.

I am Italian and Excel in Italian version works by using semicolons (;) . Now I am living in US using an Excel in English and it works by commas (,) .

This difference is not the only one. I found out there are other differences as wel

semicolons(;) and commas (,) are both correct !!

The difference depends by the Region (Country) of the user.

I am Italian and Excel in Italian version works by using semicolons (;) . Now I am living in US using an Excel in English and it works by commas (,) .

This difference is not the only one. I found out there are other differences as wel

ok i m impressed

THANKS DUDE

One more thing

I followed this " ; " instead of " , " does it makes any difference as when i use " ; " i get an error message

Please Also tell me the function of $ in excel.

I heard that it locks the cell , but can you please give me a simple example as to how can it be useful

One more think

Please tell me the Index function , as i read the Bible of Excel 2007 , i still cant figure it out the exact usage.

whats the use ?

THANKS DUDE

One more thing

I followed this " ; " instead of " , " does it makes any difference as when i use " ; " i get an error message

Please Also tell me the function of $ in excel.

I heard that it locks the cell , but can you please give me a simple example as to how can it be useful

One more think

Please tell me the Index function , as i read the Bible of Excel 2007 , i still cant figure it out the exact usage.

whats the use ?

## Vous n'êtes pas encore membre ?

**inscrivez-vous**, c'est **gratuit** et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes.

Le fait d'être membre vous permet d'avoir des options supplémentaires.

Natalista- Jul 18, 2013 12:01PM