Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > IF statement too long, need another way
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Lady_Olara
 
Posts: n/a
Default IF statement too long, need another way
How can I make this work? I keep getting the formula too long error.

=if(j4=1,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<3,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<7,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<11,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<15,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<19,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<23,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<27,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<31,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<36,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<41,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<46,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<52,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<58,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<64,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<70,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<75,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<80,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<85,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<90,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<95,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<100,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<105,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<110,sum('Upgrades-Beta'!b76:'Upgrades-eta'!b100),
if(j4<115,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
0)))))))))))))))))))))))))

TIA!
Lady Olara
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Lady_Olara
 
Posts: n/a
Default IF statement too long, need another way
Correct IF formula:
=if(j4=1,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<3,sum('Upgrades-Beta'!b77:'Upgrades-Beta'!b100),
if(j4<7,sum('Upgrades-Beta'!b78:'Upgrades-Beta'!b100),
if(j4<11,sum('Upgrades-Beta'!b79:'Upgrades-Beta'!b100),
if(j4<15,sum('Upgrades-Beta'!b80:'Upgrades-Beta'!b100),
if(j4<19,sum('Upgrades-Beta'!b81:'Upgrades-Beta'!b100),
if(j4<23,sum('Upgrades-Beta'!b82:'Upgrades-Beta'!b100),
if(j4<27,sum('Upgrades-Beta'!b83:'Upgrades-Beta'!b100),
if(j4<31,sum('Upgrades-Beta'!b84:'Upgrades-Beta'!b100),
if(j4<36,sum('Upgrades-Beta'!b85:'Upgrades-Beta'!b100),
if(j4<41,sum('Upgrades-Beta'!b86:'Upgrades-Beta'!b100),
if(j4<46,sum('Upgrades-Beta'!b87:'Upgrades-Beta'!b100),
if(j4<52,sum('Upgrades-Beta'!b88:'Upgrades-Beta'!b100),
if(j4<58,sum('Upgrades-Beta'!b89:'Upgrades-Beta'!b100),
if(j4<64,sum('Upgrades-Beta'!b90:'Upgrades-Beta'!b100),
if(j4<70,sum('Upgrades-Beta'!b91:'Upgrades-Beta'!b100),
if(j4<75,sum('Upgrades-Beta'!b92:'Upgrades-Beta'!b100),
if(j4<80,sum('Upgrades-Beta'!b93:'Upgrades-Beta'!b100),
if(j4<85,sum('Upgrades-Beta'!b94:'Upgrades-Beta'!b100),
if(j4<90,sum('Upgrades-Beta'!b95:'Upgrades-Beta'!b100),
if(j4<95,sum('Upgrades-Beta'!b96:'Upgrades-Beta'!b100),
if(j4<100,sum('Upgrades-Beta'!b97:'Upgrades-Beta'!b100),
if(j4<105,sum('Upgrades-Beta'!b98:'Upgrades-Beta'!b100),
if(j4<110,sum('Upgrades-Beta'!b99:'Upgrades-Beta'!b100),
if(j4<115,sum('Upgrades-Beta'!b100),
0)))))))))))))))))))))))))
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete_UK
 
Posts: n/a
Default IF statement too long, need another way
You can only have up to 7 nested IF statements. However, Chip Pearson
describes a way round this - here's a link to his site:

http://www.cpearson.com/excel/nested.htm

Hope this helps.

Pete

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default IF statement too long, need another way
I think this works

=IF(J4=0,0,SUM(INDIRECT("'Upgrades-Beta'!B"&IF(J4=1,76,IF(J4<31,77+INT((J4-3
)/4)+1,IF(J4<46,84+INT((J4-31)/5)+1,IF(J4<70,87+INT((J4-46)/6)+1,84+INT((J4-
70)/5)+8))))&":B100")))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Lady_Olara" wrote in message
...
Correct IF formula:
=if(j4=1,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<3,sum('Upgrades-Beta'!b77:'Upgrades-Beta'!b100),
if(j4<7,sum('Upgrades-Beta'!b78:'Upgrades-Beta'!b100),
if(j4<11,sum('Upgrades-Beta'!b79:'Upgrades-Beta'!b100),
if(j4<15,sum('Upgrades-Beta'!b80:'Upgrades-Beta'!b100),
if(j4<19,sum('Upgrades-Beta'!b81:'Upgrades-Beta'!b100),
if(j4<23,sum('Upgrades-Beta'!b82:'Upgrades-Beta'!b100),
if(j4<27,sum('Upgrades-Beta'!b83:'Upgrades-Beta'!b100),
if(j4<31,sum('Upgrades-Beta'!b84:'Upgrades-Beta'!b100),
if(j4<36,sum('Upgrades-Beta'!b85:'Upgrades-Beta'!b100),
if(j4<41,sum('Upgrades-Beta'!b86:'Upgrades-Beta'!b100),
if(j4<46,sum('Upgrades-Beta'!b87:'Upgrades-Beta'!b100),
if(j4<52,sum('Upgrades-Beta'!b88:'Upgrades-Beta'!b100),
if(j4<58,sum('Upgrades-Beta'!b89:'Upgrades-Beta'!b100),
if(j4<64,sum('Upgrades-Beta'!b90:'Upgrades-Beta'!b100),
if(j4<70,sum('Upgrades-Beta'!b91:'Upgrades-Beta'!b100),
if(j4<75,sum('Upgrades-Beta'!b92:'Upgrades-Beta'!b100),
if(j4<80,sum('Upgrades-Beta'!b93:'Upgrades-Beta'!b100),
if(j4<85,sum('Upgrades-Beta'!b94:'Upgrades-Beta'!b100),
if(j4<90,sum('Upgrades-Beta'!b95:'Upgrades-Beta'!b100),
if(j4<95,sum('Upgrades-Beta'!b96:'Upgrades-Beta'!b100),
if(j4<100,sum('Upgrades-Beta'!b97:'Upgrades-Beta'!b100),
if(j4<105,sum('Upgrades-Beta'!b98:'Upgrades-Beta'!b100),
if(j4<110,sum('Upgrades-Beta'!b99:'Upgrades-Beta'!b100),
if(j4<115,sum('Upgrades-Beta'!b100),
0)))))))))))))))))))))))))


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Arvi Laanemets
 
Posts: n/a
Default IF statement too long, need another way
Hi


=IF(OR(J4<1,J4114),0,SUM(OFFSET('Upgrades-Beta'!$B$76,MATCH(J4,{0;2;3;7;11;15;19;23;27;31;36 ;41;46;52;58;64;70;75;80;85;90;95;100;105;110},1)-1,,26-MATCH(J4,{0;2;3;7;11;15;19;23;27;31;36;41;46;52;58 ;64;70;75;80;85;90;95;100;105;110},1))))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Lady_Olara" wrote in message
...
Correct IF formula:
=if(j4=1,sum('Upgrades-Beta'!b76:'Upgrades-Beta'!b100),
if(j4<3,sum('Upgrades-Beta'!b77:'Upgrades-Beta'!b100),
if(j4<7,sum('Upgrades-Beta'!b78:'Upgrades-Beta'!b100),
if(j4<11,sum('Upgrades-Beta'!b79:'Upgrades-Beta'!b100),
if(j4<15,sum('Upgrades-Beta'!b80:'Upgrades-Beta'!b100),
if(j4<19,sum('Upgrades-Beta'!b81:'Upgrades-Beta'!b100),
if(j4<23,sum('Upgrades-Beta'!b82:'Upgrades-Beta'!b100),
if(j4<27,sum('Upgrades-Beta'!b83:'Upgrades-Beta'!b100),
if(j4<31,sum('Upgrades-Beta'!b84:'Upgrades-Beta'!b100),
if(j4<36,sum('Upgrades-Beta'!b85:'Upgrades-Beta'!b100),
if(j4<41,sum('Upgrades-Beta'!b86:'Upgrades-Beta'!b100),
if(j4<46,sum('Upgrades-Beta'!b87:'Upgrades-Beta'!b100),
if(j4<52,sum('Upgrades-Beta'!b88:'Upgrades-Beta'!b100),
if(j4<58,sum('Upgrades-Beta'!b89:'Upgrades-Beta'!b100),
if(j4<64,sum('Upgrades-Beta'!b90:'Upgrades-Beta'!b100),
if(j4<70,sum('Upgrades-Beta'!b91:'Upgrades-Beta'!b100),
if(j4<75,sum('Upgrades-Beta'!b92:'Upgrades-Beta'!b100),
if(j4<80,sum('Upgrades-Beta'!b93:'Upgrades-Beta'!b100),
if(j4<85,sum('Upgrades-Beta'!b94:'Upgrades-Beta'!b100),
if(j4<90,sum('Upgrades-Beta'!b95:'Upgrades-Beta'!b100),
if(j4<95,sum('Upgrades-Beta'!b96:'Upgrades-Beta'!b100),
if(j4<100,sum('Upgrades-Beta'!b97:'Upgrades-Beta'!b100),
if(j4<105,sum('Upgrades-Beta'!b98:'Upgrades-Beta'!b100),
if(j4<110,sum('Upgrades-Beta'!b99:'Upgrades-Beta'!b100),
if(j4<115,sum('Upgrades-Beta'!b100),
0)))))))))))))))))))))))))


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
Conditional Format as a MACRO Gunjani Excel Worksheet Functions 3 March 29th 06 05:22 PM
More than 3 conditional formats? Ltat42a Excel Discussion (Misc queries) 12 January 6th 06 11:26 AM
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM
Automatically up date time in a cell Mark Excel Discussion (Misc queries) 5 May 12th 05 12:26 AM
clock Wildman Excel Worksheet Functions 2 April 26th 05 10:31 AM


All times are GMT +1. The time now is 02:53 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"

 

Contact Us ExcelBanter forum home Privacy Statement