CODEWITHSUNDEEP
excelexcel-formula
Nick W.
Nick W.

Reputation: 1614

Use Resulting Value of One Formula Multiple Times In Cell

I am not really sure how to explain it, I am trying to run one formula to search for information and use the result multiple times in a single cell. I am currently doing this to display the queried value AND then running the same formula to find the average also.

Example Formula (Simplified)

=<FormulaToFindValue> & " (" & Round(<FormulaToFindValue>/I52,2) & "/day)"

Acutal Formula

=SUMPRODUCT((MONTH('W.A.R. 2016'!$A4:$A369)>=7)*(MONTH('W.A.R. 2016'!$A4:$A369)<=9)*('W.A.R. 2016'!$A4:$A369<TODAY())*('W.A.R. 2016'!Q4:Q369))  & " (~" & IFERROR(ROUND(SUMPRODUCT((MONTH('W.A.R. 2016'!$A4:$A369)>=7)*(MONTH('W.A.R. 2016'!$A4:$A369)<=9)*('W.A.R. 2016'!$A4:$A369<TODAY())*('W.A.R. 2016'!Q4:Q369))/B18,2),0) & "/day)"

As you can see I have to use the same Formula two times in the same cell to get the result I want, is there a way to only run the Formula once and use the resulting value multiple times? Usually this would be done by storing the value in a variable but I can't don't see similar capability in excel.

Upvotes: 5

Views: 2130

Answers (1)

Mats Lind
Mats Lind

Reputation: 934

Put the formula in a name (be careful to pick your relevant choice between fixed and dynamic references to the input ranges). Now that name can be used multiple times in a cell or in the workbook. In the example below the formula is the sum of the product between two fixed ranges and a nonsense formula in the shown cell uses this formula twice:

enter image description here

Upvotes: 3

Related Questions