MS EXCEL gurus - need your help

Forum guidelines BT super discount
Other Resources Challenge Me! » MS EXCEL gurus - need your help Rss Feed  
Moderators: the bear, kaqphin, tinkerbeth, D001, k9car363, alicefoeller Reply
2005-07-25 5:04 PM

User image

Buttercup
14334
500050002000200010010010025
Subject: MS EXCEL gurus - need your help

Does anyone know if/how I can construct a formula which will average three values but not letting any one value exceed 100%?

For example, if the 3 values are 100, 120, and 90, the formula would just average 100, 100 and 90.

Thanks in advance for your help.

2005-07-25 6:36 PM
in reply to: #206898

User image

Expert
666
5001002525
St. Thomas, ON
Subject: RE: MS EXCEL gurus - need your help

Compliments of tpetersen02, if you use this formula for your %age numbers, it will only come up to 100% max. Then your average of the percentages should be fine.

=MIN(D14/C14,1)

2005-07-25 8:08 PM
in reply to: #206898

User image

Expert
1099
1000252525
Orlando, FL
Subject: RE: MS EXCEL gurus - need your help
On my spreadsheet I took the low-tech approach. I just stopped at 100% of goal in the totals column I use for the percentage calculation. I keep actuals in another column.
2005-07-26 10:16 AM
in reply to: #206993

User image

Buttercup
14334
500050002000200010010010025
Subject: RE: MS EXCEL gurus - need your help
THANK YOU
2005-07-28 10:38 PM
in reply to: #206898

User image

Expert
1238
100010010025
Denver, Colorado
Subject: RE: MS EXCEL gurus - need your help
You could also just try a logical argument. Like, say =IF(whatever>100,100,whatever). That way, if the contents of the "whatever" cell is greater than 100% it will display 100 instead. If it's under 100% it will display its true value. Then just average the numbers after you use your logic skills on them. I guess it would take an extra column, so maybe it's not super elegant.

-dave

Edited by skavoovie 2005-07-28 10:44 PM
New Thread
Other Resources Challenge Me! » MS EXCEL gurus - need your help Rss Feed