formula error
 

Can anyone tell me what is wrong with the formula below:
=if(Data!AA910,Data!C92,(if(Data!AA910,Data!C85, (if(Data!AA780,Data!C78,(if(Data!AA710,Data!C71, (if(Data!AA640,Data!C64,(if(Data!AA570,Data!C57, (if(Data!AA430,Data!C43,(if(Data!AA360,Data!C36, (if(Data!AA220,Data!C22,(if(Data!AA150,Data!C15, Data!C8))))))))))))))))))

formula error
 

From Excel's help: "A formula can contain up to seven levels of nested
functions". You're well past double that. Half the nesting levels could be
eliminated by removing the ( before each 'if'. But you'd still be over the
limit.
Can you tell us more about what's in column AA? For example, can you be
certain that if a given cell is populated with a value 0, that all the
cells above it are also populated? Will they either have no value or a
number 0? ...


"ladyhawk" wrote:

Can anyone tell me what is wrong with the formula below:
=if(Data!AA910,Data!C92,(if(Data!AA910,Data!C85, (if(Data!AA780,Data!C78,(if(Data!AA710,Data!C71, (if(Data!AA640,Data!C64,(if(Data!AA570,Data!C57, (if(Data!AA430,Data!C43,(if(Data!AA360,Data!C36, (if(Data!AA220,Data!C22,(if(Data!AA150,Data!C15, Data!C8))))))))))))))))))

formula error
 

Hello LadyHawk,

Excel only allows you to nest 7 IF...THEN statements. You have 10. If
you must this formula in one cell, your only option is to write a User
Defined Function using VBA.

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=505036

formula error
 

I have a data sheet that lists all of the activity by day for an entire year
with a weekly summary. I then want the weekly summary to be populated on a
seperate sheet for the row that has data in it.
For instance the sheet would look at row 100 if there is no data it would
look at row 99, if there is no data in row 99 it would look at row 98 and so
on until it found the row with data in it.

"bpeltzer" wrote:

From Excel's help: "A formula can contain up to seven levels of nested
functions". You're well past double that. Half the nesting levels could be
eliminated by removing the ( before each 'if'. But you'd still be over the
limit.
Can you tell us more about what's in column AA? For example, can you be
certain that if a given cell is populated with a value 0, that all the
cells above it are also populated? Will they either have no value or a
number 0? ...


"ladyhawk" wrote:

Can anyone tell me what is wrong with the formula below:
=if(Data!AA910,Data!C92,(if(Data!AA910,Data!C85, (if(Data!AA780,Data!C78,(if(Data!AA710,Data!C71, (if(Data!AA640,Data!C64,(if(Data!AA570,Data!C57, (if(Data!AA430,Data!C43,(if(Data!AA360,Data!C36, (if(Data!AA220,Data!C22,(if(Data!AA150,Data!C15, Data!C8))))))))))))))))))

formula error
 

Well you certainly can't nest 100 IFs ;-)
I'd suggest instead something along the following lines:
Use the COUNT function to determine how many rows of data you've got, then
calculate from that the number of weekly totals you'd have, from that the row
with the latest weekly total, then use INDEX to get the data back from that
row.
Somethling like: =index(C:C,7*int(count(AA:AA)/7)+1).

"ladyhawk" wrote:

I have a data sheet that lists all of the activity by day for an entire year
with a weekly summary. I then want the weekly summary to be populated on a
seperate sheet for the row that has data in it.
For instance the sheet would look at row 100 if there is no data it would
look at row 99, if there is no data in row 99 it would look at row 98 and so
on until it found the row with data in it.

"bpeltzer" wrote:

From Excel's help: "A formula can contain up to seven levels of nested
functions". You're well past double that. Half the nesting levels could be
eliminated by removing the ( before each 'if'. But you'd still be over the
limit.
Can you tell us more about what's in column AA? For example, can you be
certain that if a given cell is populated with a value 0, that all the
cells above it are also populated? Will they either have no value or a
number 0? ...


"ladyhawk" wrote:

Can anyone tell me what is wrong with the formula below:
=if(Data!AA910,Data!C92,(if(Data!AA910,Data!C85, (if(Data!AA780,Data!C78,(if(Data!AA710,Data!C71, (if(Data!AA640,Data!C64,(if(Data!AA570,Data!C57, (if(Data!AA430,Data!C43,(if(Data!AA360,Data!C36, (if(Data!AA220,Data!C22,(if(Data!AA150,Data!C15, Data!C8))))))))))))))))))

formula error
 

Ok I think we are getting closer but I am still confused. Since my last post
I have made a spot on the sheet where all of the weekly totals are in 52
consecutive rows. Can u suggest a formula to put whichever the last row that
has data in it into a seperate sheet?

"bpeltzer" wrote:

Well you certainly can't nest 100 IFs ;-)
I'd suggest instead something along the following lines:
Use the COUNT function to determine how many rows of data you've got, then
calculate from that the number of weekly totals you'd have, from that the row
with the latest weekly total, then use INDEX to get the data back from that
row.
Somethling like: =index(C:C,7*int(count(AA:AA)/7)+1).

"ladyhawk" wrote:

I have a data sheet that lists all of the activity by day for an entire year
with a weekly summary. I then want the weekly summary to be populated on a
seperate sheet for the row that has data in it.
For instance the sheet would look at row 100 if there is no data it would
look at row 99, if there is no data in row 99 it would look at row 98 and so
on until it found the row with data in it.

"bpeltzer" wrote:

From Excel's help: "A formula can contain up to seven levels of nested
functions". You're well past double that. Half the nesting levels could be
eliminated by removing the ( before each 'if'. But you'd still be over the
limit.
Can you tell us more about what's in column AA? For example, can you be
certain that if a given cell is populated with a value 0, that all the
cells above it are also populated? Will they either have no value or a
number 0? ...


"ladyhawk" wrote:

Can anyone tell me what is wrong with the formula below:
=if(Data!AA910,Data!C92,(if(Data!AA910,Data!C85, (if(Data!AA780,Data!C78,(if(Data!AA710,Data!C71, (if(Data!AA640,Data!C64,(if(Data!AA570,Data!C57, (if(Data!AA430,Data!C43,(if(Data!AA360,Data!C36, (if(Data!AA220,Data!C22,(if(Data!AA150,Data!C15, Data!C8))))))))))))))))))

formula error
 

If the range of potential weekly totals is in, say, Weekly!A2:Z54, and you
know that column A is blank until the row is populated and thereafter
contains a number (eg a date), then I would use something like
=index(Weekly!A$2:A$54,count(Weekly!$A$2:$A$54)). You could autofill that
formula into the remaining columns to show the full row of data from the
latest week.
--Bruce

"ladyhawk" wrote:

Ok I think we are getting closer but I am still confused. Since my last post
I have made a spot on the sheet where all of the weekly totals are in 52
consecutive rows. Can u suggest a formula to put whichever the last row that
has data in it into a seperate sheet?

"bpeltzer" wrote:

Well you certainly can't nest 100 IFs ;-)
I'd suggest instead something along the following lines:
Use the COUNT function to determine how many rows of data you've got, then
calculate from that the number of weekly totals you'd have, from that the row
with the latest weekly total, then use INDEX to get the data back from that
row.
Somethling like: =index(C:C,7*int(count(AA:AA)/7)+1).

"ladyhawk" wrote:

I have a data sheet that lists all of the activity by day for an entire year
with a weekly summary. I then want the weekly summary to be populated on a
seperate sheet for the row that has data in it.
For instance the sheet would look at row 100 if there is no data it would
look at row 99, if there is no data in row 99 it would look at row 98 and so
on until it found the row with data in it.

"bpeltzer" wrote:

From Excel's help: "A formula can contain up to seven levels of nested
functions". You're well past double that. Half the nesting levels could be
eliminated by removing the ( before each 'if'. But you'd still be over the
limit.
Can you tell us more about what's in column AA? For example, can you be
certain that if a given cell is populated with a value 0, that all the
cells above it are also populated? Will they either have no value or a
number 0? ...


"ladyhawk" wrote:

Can anyone tell me what is wrong with the formula below:
=if(Data!AA910,Data!C92,(if(Data!AA910,Data!C85, (if(Data!AA780,Data!C78,(if(Data!AA710,Data!C71, (if(Data!AA640,Data!C64,(if(Data!AA570,Data!C57, (if(Data!AA430,Data!C43,(if(Data!AA360,Data!C36, (if(Data!AA220,Data!C22,(if(Data!AA150,Data!C15, Data!C8))))))))))))))))))