Find components of an Average Result

[al_ba]

Hello,

I have data below, On D1 if I put the formula =AVERAGE(A1:C1) it will return
80; the input data are A1 to C1
A B C D
1 75 80 85 80

My question is...is there a way/ possible to "reverse" this process, where I
will input 80 (or any value) on D1 and it will give the values in A1 to C1
(which are not equal to each other, like in my example)?


Thanks in advance for your help!

 

[Ashish Mathur]

Hi,

For that to happen, you have have to define a relation between A, B, C and
D. Alternatively, if any two of the 3 values can be fixed, the third can be
back calculated by using Goal Seek

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Pete_UK]

There will be an infinite number of those numbers with an average of
80, eg 79, 80, 81 - 78, 80, 82 - 77, 80, 83 etc. just to show 3 sets.
How to determine which is the right set?

Pete

 

[al_ba]

Hello,

That would be +5, (like my example)...if possible can be changed to +3 (or
any number). Thanks in advance.

 

[Pete_UK]

Use F1 to specify the spacing (i.e. 5 in this case), then you can have
these formulae:

A1: =D1-F1
B1: =D1
C1: =D1+F1

Hope this helps.

Pete

 

[al_ba]

Hi Pete,

That's a lot of help, thanks!

Use F1 to specify the spacing (i.e. 5 in this case), then you can have
these formulae:

A1: =D1-F1
B1: =D1
C1: =D1+F1

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - strange request, though !!

Pete