If (cell) is (range of cells), then (cell) should be (range of cells)

[ExcelPatient]

Hi.



I am in need of a formula that converts a value in a specific cell to a value that lies within a range of cells automatically.

For example, if (P5) is "3.3" (the average of P2-P4), then (P6) should be (C25) (sorry you can't see this...screenshot cut off after C20)

The added complexity is that I will be regularly modifying the values in P2-P4, so the formula in P6 needs to update automatically.

So, if I change values in P2-P4, and say P5 now reads "4.8", then P6 would need to automatically be "96" in (C11).

See picture, below.

THANKS.


Screen Shot 2016-11-06 at 8.46.59 PM.png

[xor]

Vlookup($p$5,$a$9:$b$59,2,0)

[ExcelPatient]

Awesome. Very helpful.

Now, a new issue.

(Q5) is reading an error because (Q4) is a formula that calculates the average of (Q1-Q3) and its result contains a number to the millionth, which the VLookup in (Q5) cannot find. Obviously, I cannot include all possible divisions between 1 and 5 inside (A8:B21).

(Q4) formula reads: =IF(COUNT(Q1:Q3)>0,AVERAGE(Q1:Q3),"")

See picture, below.

Screen Shot 2016-11-07 at 8.01.33 PM.png

[xor]

Take a look at the attached.

[ExcelPatient]

Hi. That totally worked!

Last question: when values to be averaged are not entered and cells are consequently left blank, there are divide by zero error messages showing. Only when I enter at least one value does the result display and these error messages disappear.

In the following formulas, is there a way to show a blank cell, not a divide by zero error, when no values to be averaged are entered?

=ROUND(AVERAGE(Q5:Q7),1)
=Vlookup(Q8, $B$38:$C$78,2,)

Screen Shot 2016-11-08 at 7.38.24 AM.png

[xor]

=iferror(round(average(q5:q7),1),"")

=iferror(vlookup(q8,$b$38:$c$78,2,0),"")