A different formula similar to an If statement

[FedElecQaEng]

I make use of the If statement a lot and sometimes the formula returns a 0. When the main formula returns a zero I want it to show the dash character so I usually make a formula like this:

=If(*Formula* = 0,"-",*Formula*)

My main question is if there is a way to not have to repeat the formula twice especially since sometimes the main formula can fill up more than half the formula bar, and sometimes I make changes to the main formula (making the same change twice multiple times can get annoying and time-consuming).


Something I would like is similar to the IFERROR function where, if the formula returns an error then return a different value, else return the formula value (In these cases I also make use of the "-" character)
Following the IFERROR format, a made-up formula that I can imagine as an example would be like =IFVAL([logical formula],[value],[value_if_same]). So if [logical formula] returns the value in [value] then IFVAL returns the [value_if_same] else IFVAL returns the value from [logical formula]

 

[Fluff]

How about

=IFERROR(1/(1/(your formula)),"-")

 

[JamesCanale]

You might use custom formatting instead:

MrExcelPlayground20.xlsx
	I	J
10	1	4
11	2	3
12	3	2
13	4	1
14	5	-
15	6	-1
16	7	-2
Sheet16

Cell Formulas
Range	Formula
J10:J16	J10	=5-I10

If you are looking to change 0 specifically, then

MrExcelPlayground20.xlsx
	I	J	K
10	1	4	4
11	2	3	3
12	3	2	2
13	4	1	1
14	5	-	-
15	6	-1	-1
16	7	-2	-2
Sheet16

Cell Formulas
Range	Formula
J10:J16	J10	=5-I10
K10:K16	K10	=IFERROR(1/(1/(5-I10)),"-")

 

[FedElecQaEng]

That IFERROR formula is good when I am using just numerical values (and I thank you for that and will try to remember it when I come across it) but mostly I make use of strings and characters.

My formula use INDEX-MATCH alot for searching tables of data and some of the cells in the data tables are blank which in turn returns a 0. It is these instances that i want a "-" character.

The main sheet has the following formula used

=IFERROR(IF(INDEX('SHEET REFS'!$A:$Z,INDEX(GLOBAL!$AB:$AC,MATCH($H7,GLOBAL!$AB:$AB,0),2),MATCH(Q$3,'SHEET REFS'!$1:$1,0)) = 0,"-",INDEX('SHEET REFS'!$A:$Z,INDEX(GLOBAL!$AB:$AC,MATCH($H7,GLOBAL!$AB:$AB,0),2),MATCH(Q$3,'SHEET REFS'!$1:$1,0))),"-")

And would return the following as examples (I have conditional formatting to fills cells with "-" to gray)

'SHEET REF' contains the following formulas:

=INDIRECT("'" & GLOBAL!$I$3 & "'!" & CHAR(64 + COLUMN(P6)) & ROW(P6))

And has the following values:

(the GLOBAL sheet mainly contains names of other different sheets of data tables that i use SHEET REF to switch between when selections on the main sheet are made)

To finalize, sometimes cells in the comment column on SHEET REF contains a string so i want it to have that string on the main sheet, but when a cell on SHEET REF contains a 0 because of where it pulled the data from then on the main sheet i want to display a "-"

My question is if there is a way to perform the same function as the first formula on the main sheet without having to repeat the formula twice.

 

[Fluff]

You could use

=T(INDEX('SHEET REFS'!$A:$Z,INDEX(GLOBAL!$AB:$AC,MATCH($H7,GLOBAL!$AB:$AB,0),2),MATCH(Q$3,'SHEET REFS'!$1:$1,0)))

but this will return "" rather than "-"

 

[Fluff]

And would return the following as examples (I have conditional formatting to fills cells with "-" to gray)

Another option is to just change the CF from cells with "-" to cells with 0

 

[FedElecQaEng]

Thank you for the advice.
The T Function worked for the cells with text however some of the cells have dates and since it is a number excel was clearing out the value. The column with the date might change (based on the data table it is pulled from) so I cannot make a column specific to dates. For this specific instance, changing CF to cells with 0 worked, thank you.

The T function is useful though and I will make sure to remember that formula as it might be useful in other situations (on a side note is there a similar formula but for numeric instead)

What I will assume though is my main question of simplifying the IF function might not be possible. Sometimes the comparison value is not a 0 but rather a specific word.
An example formula I can think of that I would like to simplify would be =IF([Logical #1] = "CLOSED",[Logical #2],[Logical #1]). So if [Logical #1] is equal to "CLOSED" then perform [Logical #2] but if not then return the value from [Logical #1].
A simplified version that I am looking for would only need to have [Logical #1] appear once in the entire formula instead of twice.

 

[Fluff]

With xl2010 I don't think you have much option other than to use what you have already got.

 

[FedElecQaEng]

2010 is what I was provided with by my company, but I might be able to request a later version.

Any version that you can recommend and what i can use in that version?

Secondary question to a newer version, if someone else has Excel 2010 can they open/use a file that I create in the newer version?

 

[Fluff]

With either 2021 or 365 you have the LET function which would do what you want, but it will not work for anyone on an older version.