Question

[odditie]

Hey,

I have a pretty complicated question, it's probably easy for well versed users, but I'm not sure what the cleanest way to do this is.

Here's what I'm doing. I'm taking a column of numbers, then in the column next to it I want to do the following.

=(B2/(AVERAGE(B2:B29))*75)

I've figured it out to that point, but rather than adjusting each of the other cells when I use the format painter to find this value in the column, I'd like to just be able to format them all the same and have them all find their value.

I hope this explains what I'm trying to do well enough, I couldn't really put it into words.

Thank you

[Trevor Shuttleworth]

Try:

=B2/AVERAGE(B$2:B$29)*75

and drag down to Row 29

Regards

Trevor


"odditie" <[email protected]> wrote in
message news:[email protected]...
>
> Hey,
>
> I have a pretty complicated question, it's probably easy for well
> versed users, but I'm not sure what the cleanest way to do this is.
>
> Here's what I'm doing. I'm taking a column of numbers, then in the
> column next to it I want to do the following.
>
> =(B2/(AVERAGE(B2:B29))*75)
>
> I've figured it out to that point, but rather than adjusting each of
> the other cells when I use the format painter to find this value in the
> column, I'd like to just be able to format them all the same and have
> them all find their value.
>
> I hope this explains what I'm trying to do well enough, I couldn't
> really put it into words.
>
> Thank you
>
>
> --
> odditie
> ------------------------------------------------------------------------
> odditie's Profile:
> http://www.excelforum.com/member.php...o&userid=18151
> View this thread: http://www.excelforum.com/showthread...hreadid=567743
>

[odditie]

AH yes, I've done that before, I forgot all about that!

Thank you very much

[odditie]

Another question with the same formula.

How do I set it to have minimum and maximum values?

I want the resulting value to be between 60 and 90

[Trevor Shuttleworth]

Not quite an average then ;-)

=IF(B2/AVERAGE(B$2:B$29)*75<60,60,IF(B2/AVERAGE(B$2:B$29)*75>90,90,B2/AVERAGE(B$2:B$29)*75))

Regards

Trevor


"odditie" <[email protected]> wrote in
message news:[email protected]...
>
> Another question with the same formula.
>
> How do I set it to have minimum and maximum values?
>
> I want the resulting value to be between 60 and 90
>
>
> --
> odditie
> ------------------------------------------------------------------------
> odditie's Profile:
> http://www.excelforum.com/member.php...o&userid=18151
> View this thread: http://www.excelforum.com/showthread...hreadid=567743
>

[Ragdyer]

How about:

=MIN(90,MAX(B2/AVERAGE(B$2:B$29)*75,60))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Trevor Shuttleworth" <[email protected]> wrote in message
news:[email protected]...
> Not quite an average then ;-)
>
> =IF(B2/AVERAGE(B$2:B$29)*75<60,60,IF(B2/AVERAGE(B$2:B$29)*75>90,90,B2/AVERAGE(B$2:B$29)*75))
>
> Regards
>
> Trevor
>
>
> "odditie" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Another question with the same formula.
>>
>> How do I set it to have minimum and maximum values?
>>
>> I want the resulting value to be between 60 and 90
>>
>>
>> --
>> odditie
>> ------------------------------------------------------------------------
>> odditie's Profile:
>> http://www.excelforum.com/member.php...o&userid=18151
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=567743
>>
>
>

[Trevor Shuttleworth]

That's neat


"Ragdyer" <[email protected]> wrote in message
news:[email protected]...
> How about:
>
> =MIN(90,MAX(B2/AVERAGE(B$2:B$29)*75,60))
>
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Trevor Shuttleworth" <[email protected]> wrote in message
> news:[email protected]...
>> Not quite an average then ;-)
>>
>> =IF(B2/AVERAGE(B$2:B$29)*75<60,60,IF(B2/AVERAGE(B$2:B$29)*75>90,90,B2/AVERAGE(B$2:B$29)*75))
>>
>> Regards
>>
>> Trevor
>>
>>
>> "odditie" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Another question with the same formula.
>>>
>>> How do I set it to have minimum and maximum values?
>>>
>>> I want the resulting value to be between 60 and 90
>>>
>>>
>>> --
>>> odditie
>>> ------------------------------------------------------------------------
>>> odditie's Profile:
>>> http://www.excelforum.com/member.php...o&userid=18151
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=567743
>>>
>>
>>
>