IF statement efficiency

[ihrigt]

I'm trying to make sure that a huge range of IF statements evaluate as quickly and efficiently as possible. Each cell that I'm testing has either a number or a "-" if there is no data. On the presentation sheet, I want to show a blank cell when there is no data.

So, the IF statements all say =IF(SourceCell="-", "", SourceCell)

However, in my actual spreadsheet, the "SourceCell" is actually a complicated formula using MATCH and OFFSET to figure out which cell to test. So, my question: The core formula has to evaluate the "SourceCell" formula twice; is there a better way that only causes the "SourceCell" formula to be evaluated once?

 

[xanksx]

Do not use the huge formula in the If function.
Instead your Sourecell should be the cell in which the huge formula is.

This way the huge formula will calculate only once, and your If function will only check if the calculated result is true or false.

 

[lenze]

Or, you could use a Named Formula instead of a cell reference

lenze

 

[Scott Huish]

Or perhaps, you could simplify your formula.

Could you post your formula and explain what you are trying to do exactly?

What kind of data is SourceCell returning, text or numeric?

 

[ihrigt]

Thanks to both for the quick replies! I thought about creating an area (or another sheet) that was the "SourceCell" formulas, but given the size of the sheet I'm working with it becomes a tradeoff between efficiency and space/size.

I think I understand your named formula idea, setting a name up to represent my "SourceCell" formula. But then isn't my IF statement still =IF(NamedSource="-", "", NamedSource)? In other words, Excel still has to evaluate NamedSource twice, yes?

 

[Scott Huish]

You may not need any of those IF formulas. What kind of data is your SourceCell formula returning, numeric or text? Is it returning a number or "" ?

 

[ihrigt]

Essentially, the source data is a set of characteristics about an item, most are numeric and a few are text. The data is downloaded from an external source that delivers the data in a fixed format which is cut-pasted into a "Raw Data" sheet in the workbook.

The purpose of the "Presentation" sheet is to clean up the data in a variety of ways and make it more user friendly. For example, for some reason one column of percentages comes through as 5645 (for example) instead of 56.45%. So, the formula has to divide by 1000 to get it in the proper context. And, as I mentioned, any data point that does not have a value has a "-" in the source data.

In the "Presentation" sheet, column B houses the item codes and the rest of the columns bring in the individual data points (in a different order than the source data). So the core of the formula in every cell is similar to:

=IF(ISNA(MATCH($B8,'Raw Data'!$A$3:$A$5000,0)),"",
IF(OFFSET('Raw Data'!$A$2,MATCH($B8,'Raw Data'!$A$3:$A$5000,0),15)="-","",
OFFSET('Raw Data'!$A$2,MATCH($B8,'Raw Data'!$A$3:$A$5000,0),15)))

In this example, the MATCH checks the item code to see if it exists in the source data and if so, it then checks to see if the data in the 15th column of that matching row is "-". If so, it returns "" otherwise it returns the data. Each column in the "Presentation" sheet links to a specific column of the "Raw Data" sheet. This is a pain too since the order of the "Raw Data" has been known to change causing me to have to adjust every formula.

Since all of the columns in the "Presentation" sheet refer to the same MATCH formula, I've placed that in a hidden column to simplify all the formulas. I'm just wondering if there is a way to avoid evaluating the OFFSET twice across the entire sheet - perhaps CPU cycles are so fast it doesn't matter and this brute force method is fine.

I'm an old programmer and the solution in a programming language is simple. Set a variable = OFFSET(); =IF(variable = "-","",variable). This is essentially what xanksx proposed earlier, but I'm simply wondering if there is a formulaic way to avoid the double evaluation. Thanks again.