IF function - is there a way to avoid repeating fo

问题:

Can't believe I don't know this, but is there a way to avoid repeating a formula in an if statement if the logical test is dependent on it?

i.e.

=IF((SUMIFS formula)=0,"",SUMIFs formula)

I want to replace that SUMIFS function in the false scenario with something short that will tell it to just programmatically repeat the formula it originally tested for. Repeating the formula twice has to have detrimental effects on processing speed. Negligible, maybe, but want to go for best-practices here. Thanks.

回答1:

You can assign a Name to a formula and use the Name..............See:

Assigning a name to a formula

Relevant excerpt -

For example, let's suppose we frequently use a formula like: =SUM(A1:A100)-SUM(B1:B100) and this resides in A101 and is copied across many columns on row 101. It would be better in this case to create a custom formula that does this in each cell on row 101. Here is how;

1) Select cell A101 (this is vital).

2) Go to Insert>Name>Define and in the "Names in workbook" box type: SalesLessCosts

3) Now click in the "Refers to" box and type: =SUM(A1:A100)-SUM(B1:B100) then click Add.

Now you can replace the formula in cell A101 with: =SalesLessCosts. You can also copy this across row 101 and it will change its relative references just as the formula =SUM(A1:A100)-SUM(B1:B100) would. The reason it does this is all down to the fact we selected A101 before going to Insert>Name>Define and used relative references in =SUM(A1:A100)-SUM(B1:B100) when we added it to the "Refers to" box.

回答2:

You can force an error like #DIV/0! and then use IFERROR, e.g.

=IFERROR(1/(1/SUMIFS_formula),"")

回答3:

If all you need to do is hide zeroes, there is an easy way:

• Select all cells where you wish to hide zeroes
• Go into Custom Number Formatting
• Set format to "General;General;"

The custom formatting has a structure of [positive numbers];[negative numbers];[zeroes] By making the last part blank you are effectively hiding zeroes, but showing everything else.

The advantage over conditional formatting is that you can use this on any background.

A neat trick which I sometimes use is to hide the cell value completely by using a custom format of ";;;". This way you can put images inside the cells, like the conditional formatting ones, and not see the value at all.

回答4:

Try using the SUBSTITUTE function like this :

=SUBSTITUTE( VLOOKUP( H4; $D$5:$E$8; 2; 0 ); $H$1; $I$1 )

Here is an example:

Here the formula I don't want to repeat twice is the VLOOKUP function. The result of VLOOKUP is a string found in another table (ex : "Green").

I want to check if that string matches a specific string value in $H$1 (here, "Yellow").

• If it does, SUBSTITUTE replaces it with$I$1 (the error string you want. Here, "FORBIDDEN").
• If it doesn't, it displays the VLOOKUP result string (the normal authorized output, like "Green").

This is useful for me because my actual formula is quite long, so I don't want to write it twice. I also dont want to use two different cells, because I'm already applying this formula on 10 columns, meaning I should add an extra 10 columns to make it work.

回答5:

Here is a hack - depending on whether you are just interested in the displayed value, or whether you need to use the value in another formula:

Put your SUMIF formula in the cell (without the IF part)

Create a conditional formatting rule which sets the font color to the background color when the cell value is 0

And hey presto, you get the desired result.

As I said - it's a hack, but it does prevent the double evaluation.

There is no "clean" solution that I am aware of.

回答6:

In some scenarios, MAX() or MIN() can do a wonderful job.

E.g., something like this:

=IF(SUMIFSformula>0,SUMIFSformula, 0)

Can be shortened to this:

=MAX(0,SUMIFSformula)

回答7:

Actually, since Excel 2007, the IFERROR statement does exactly what the OP asked. From the help file:

Description: Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. [italics mine]

Syntax: IFERROR(value, value_if_error)