Preventing errors
 

Hi all

I have a s/sheet in the middle of being set up.

I need to create a formula based on columns E, G & I. I need Column K
to average the percentage of those previous 3 columns.

Column I will be presented in a number format (1-5) these are converted
to percentages (see field K18 for an example of what each number is
converted to)

The problem I'm facing is this.........I want the formula to be pretty
robust so that if for any reason, columns E, G or I (or any combination
of the 3) are not completed, the average percentage in column K is still
calculated.

For example, if I populate column E18 with 100%, G18 leave blank and
I18 insert a 3, column K appears as 100%. This is clearly incorrect as
the average of 100% and 50% (where 3 = 50%) should be 75% !!!

Any help please ?


+-------------------------------------------------------------------+
|Filename: 1107Surveys(store)2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4353 |
+-------------------------------------------------------------------+

--
koit
------------------------------------------------------------------------
koit's Profile: http://www.excelforum.com/member.php...o&userid=31100
View this thread: http://www.excelforum.com/showthread...hreadid=511998

Preventing errors
 

Please, any help ?


--
koit
------------------------------------------------------------------------
koit's Profile: http://www.excelforum.com/member.php...o&userid=31100
View this thread: http://www.excelforum.com/showthread...hreadid=511998

Preventing errors
 

Please, anyone kind enough to help out ?


--
koit
------------------------------------------------------------------------
koit's Profile: http://www.excelforum.com/member.php...o&userid=31100
View this thread: http://www.excelforum.com/showthread...hreadid=511998

Preventing errors
 

Can someone please help me with this !!


--
koit
------------------------------------------------------------------------
koit's Profile: http://www.excelforum.com/member.php...o&userid=31100
View this thread: http://www.excelforum.com/showthread...hreadid=511998

Preventing errors
 

Many people are reluctant to download files from an unknown source - it
is better to try to describe your problem with example data in plain
text on the newsgroups, so that is probably why you have had no
responses.

However, in your example I would suggest you enter this formula in H6:

=i6/5

format as percentage and copy down. If you really want this column to
look blank, then format the foreground colour to white. Then in J6
enter this formula:

=AVERAGE(IF(E6:H6<0,E6:H6))

Do not press <ENTER when you have typed this is - instead press
<CTRL<SHIFT<ENTER at the same time, as this is an array formula. If
you do it correctly then Excel will wrap curly braces { } around the
formula - you should not type these yourself. You can then copy this
formula down the column.

Hope this helps.

Pete

Preventing errors
 

If 0% is a valid entry for column E and G (i.e. you want to include
this in your average) then you will need to make the following
amedments:

H6: =IF(i6="","",i6/5)

and copy down.

J6: =AVERAGE(IF(ISNUMBER(E6:H6),E6:H6))

commit this with <CTRL<SHIFT<ENTER as before, then copy down. The
average will now be taken of any numeric values in the range.

Hope this helps.

Pete

Preventing errors
 

Pete

Thanks ever so much for your response, with which I have achieved my
goal.

I take on board your comments regarding posting files...........

Thanks once again.

Koit


--
koit
------------------------------------------------------------------------
koit's Profile: http://www.excelforum.com/member.php...o&userid=31100
View this thread: http://www.excelforum.com/showthread...hreadid=511998

Preventing errors
 

Well, your posts looked so lonely on their own! Thank you for feeding
back.

Pete