Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Qaspec
 
Posts: n/a
Default 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?
  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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?


  #3   Report Post  
Bob Phillips
 
Posts: n/a
Default

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?



  #4   Report Post  
K.S.Warrier
 
Posts: n/a
Default

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?




  #5   Report Post  
Bob Phillips
 
Posts: n/a
Default

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?








  #6   Report Post  
zeroscout
 
Posts: n/a
Default

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?






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Overdue function, Macro, or VBS Galsaba Excel Discussion (Misc queries) 5 January 14th 05 01:26 AM
Macro or Function to make text size to suite text Length? lbbss Excel Discussion (Misc queries) 4 December 14th 04 08:53 PM
Record Macro Relative does not work? lbbss Excel Discussion (Misc queries) 1 December 13th 04 08:55 PM
I cant use englisch function names in a swedich version of excel PE Excel Discussion (Misc queries) 2 December 7th 04 02:00 AM
Macro and If Statement SATB Excel Discussion (Misc queries) 2 December 3rd 04 05:46 PM


All times are GMT +1. The time now is 11:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"