Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)))))))))))))))))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)))))))))))))))))) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)))))))))))))))))) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)))))))))))))))))) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)))))))))))))))))) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)))))))))))))))))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I get error with "ROWS" in the formula - nested formula question | Excel Worksheet Functions | |||
formula too long error | Excel Discussion (Misc queries) | |||
Simplifying array formula which evaluates as error. | Excel Worksheet Functions | |||
Formula syntax error - chinese and gibberish | Excel Discussion (Misc queries) | |||
Formula error... | Excel Discussion (Misc queries) |