Function Macro for Nested IF
 

I need to create a macro to "break" my statement to get the information i
need. I want to create a macro that will allow me to average data each month
as each month in the year goes by. Here is the formula I was attempting:
=IF(E10="",D10,IF(F10="",SUM(D10+E10)/2,IF(G10="",SUM(D10:F10)/3,IF(H10="",SUM(D10:G10)/4,IF(I10="",SUM(D10:H10)/5,IF(J10="",SUM(D10:I10)/6,IF(K10="",SUM(D10:J10)/7,IF(L10="",SUM(D10:K10)/8,...."
Once I get past the 7th nested IF the formula stops working. So if "D10 =
January" what is an example of a function macro that would work?

Hi
why not use
=AVERAGE(D10:X10)
AVERAGE will ignore empty cells

--
Regards
Frank Kabel
Frankfurt, Germany

"Qaspec" schrieb im Newsbeitrag
...
I need to create a macro to "break" my statement to get the
information i
need. I want to create a macro that will allow me to average data
each month
as each month in the year goes by. Here is the formula I was
attempting:

=IF(E10="",D10,IF(F10="",SUM(D10+E10)/2,IF(G10="",SUM(D10:F10)/3,IF(H10
="",SUM(D10:G10)/4,IF(I10="",SUM(D10:H10)/5,IF(J10="",SUM(D10:I10)/6,IF
(K10="",SUM(D10:J10)/7,IF(L10="",SUM(D10:K10)/8,...."
Once I get past the 7th nested IF the formula stops working. So if
"D10 =
January" what is an example of a function macro that would work?

Just

=AVERAGE(D10:Z10)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Qaspec" wrote in message
...
I need to create a macro to "break" my statement to get the information i
need. I want to create a macro that will allow me to average data each
month
as each month in the year goes by. Here is the formula I was attempting:

=IF(E10="",D10,IF(F10="",SUM(D10+E10)/2,IF(G10="",SUM(D10:F10)/3,IF(H10="",S
UM(D10:G10)/4,IF(I10="",SUM(D10:H10)/5,IF(J10="",SUM(D10:I10)/6,IF(K10="",SU
M(D10:J10)/7,IF(L10="",SUM(D10:K10)/8,...."
Once I get past the 7th nested IF the formula stops working. So if "D10 =
January" what is an example of a function macro that would work?

try this-
Instead of nesting the details for 12 months,use + as shown below
=if(e10="",d10,0)+if(f10="",sum(d10+e10)/2,0)+if(g10="",sum(d10:f10)/3,0)+........up to required datas.
K.S.Warrier

"Bob Phillips" wrote:

Just

=AVERAGE(D10:Z10)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Qaspec" wrote in message
...
I need to create a macro to "break" my statement to get the information i
need. I want to create a macro that will allow me to average data each
month
as each month in the year goes by. Here is the formula I was attempting:

=IF(E10="",D10,IF(F10="",SUM(D10+E10)/2,IF(G10="",SUM(D10:F10)/3,IF(H10="",S
UM(D10:G10)/4,IF(I10="",SUM(D10:H10)/5,IF(J10="",SUM(D10:I10)/6,IF(K10="",SU
M(D10:J10)/7,IF(L10="",SUM(D10:K10)/8,...."
Once I get past the 7th nested IF the formula stops working. So if "D10 =
January" what is an example of a function macro that would work?

Why, when Average does the job?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"K.S.Warrier" wrote in message
...
try this-
Instead of nesting the details for 12 months,use + as shown below

=if(e10="",d10,0)+if(f10="",sum(d10+e10)/2,0)+if(g10="",sum(d10:f10)/3,0)+..
.......up to required datas.
K.S.Warrier

"Bob Phillips" wrote:

Just

=AVERAGE(D10:Z10)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Qaspec" wrote in message
...
I need to create a macro to "break" my statement to get the
information i
need. I want to create a macro that will allow me to average data each
month
as each month in the year goes by. Here is the formula I was
attempting:


=IF(E10="",D10,IF(F10="",SUM(D10+E10)/2,IF(G10="",SUM(D10:F10)/3,IF(H10="",S

UM(D10:G10)/4,IF(I10="",SUM(D10:H10)/5,IF(J10="",SUM(D10:I10)/6,IF(K10="",SU
M(D10:J10)/7,IF(L10="",SUM(D10:K10)/8,...."
Once I get past the 7th nested IF the formula stops working. So if
"D10 =
January" what is an example of a function macro that would work?

Would =subtotal(1,D10:X10) be a better choice than =average(D10:X10)?

-zero

"Bob Phillips" wrote:

Why, when Average does the job?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"K.S.Warrier" wrote in message
...
try this-
Instead of nesting the details for 12 months,use + as shown below

=if(e10="",d10,0)+if(f10="",sum(d10+e10)/2,0)+if(g10="",sum(d10:f10)/3,0)+..
.......up to required datas.
K.S.Warrier

"Bob Phillips" wrote:

Just

=AVERAGE(D10:Z10)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Qaspec" wrote in message
...
I need to create a macro to "break" my statement to get the
information i
need. I want to create a macro that will allow me to average data each
month
as each month in the year goes by. Here is the formula I was
attempting:


=IF(E10="",D10,IF(F10="",SUM(D10+E10)/2,IF(G10="",SUM(D10:F10)/3,IF(H10="",S

UM(D10:G10)/4,IF(I10="",SUM(D10:H10)/5,IF(J10="",SUM(D10:I10)/6,IF(K10="",SU
M(D10:J10)/7,IF(L10="",SUM(D10:K10)/8,...."
Once I get past the 7th nested IF the formula stops working. So if
"D10 =
January" what is an example of a function macro that would work?