Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 22nd 05, 09:17 PM
SUB-ZERO
 
Posts: n/a
Default # of Functions per cell

Hi

Is there any way to change the number of functions allowed in each cell?
I have a spreadsheet and I need it to handle more functions than 7.
10-15 functions wolud really work well for me.
Here is one example of what im working with, I need to have a few more
allowed functions for the formula to work correctly.
=IF(D11=0,D12,IF(D10=0,SUM(D1112),IF(D9=0,SUM(D1 012),IF(D8=0,SUM(D912),IF(D7=0,SUM(D812),IF( D6=0,SUM(D712),IF(D5=0,SUM(D612),SUM(D512))) )))))

Any help would be greatly appreciated

Thanks ...


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-


  #2   Report Post  
Old January 22nd 05, 10:34 PM
RagDyeR
 
Posts: n/a
Default

Don't know if your "empty" cells are,
blank,
or null ( "" )
or
zeroes,
So, this may or may not work for you:

=IF(ISNA(LOOKUP(9.99999999999999E+307,D111)),D12 ,LOOKUP(9.99999999999999E+
307,D111)+D12)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


contain 0's, or nulls < "" , or
"SUB-ZERO" wrote in message
...
Hi

Is there any way to change the number of functions allowed in each cell?
I have a spreadsheet and I need it to handle more functions than 7.
10-15 functions wolud really work well for me.
Here is one example of what im working with, I need to have a few more
allowed functions for the formula to work correctly.
=IF(D11=0,D12,IF(D10=0,SUM(D1112),IF(D9=0,SUM(D1 012),IF(D8=0,SUM(D912)
,IF(D7=0,SUM(D812),IF(D6=0,SUM(D712),IF(D5=0,S UM(D612),SUM(D512)))))
)))

Any help would be greatly appreciated

Thanks ...


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-


  #3   Report Post  
Old January 23rd 05, 07:57 PM
John Mansfield
 
Posts: n/a
Default

To get around the "nested 7 levels deep" IF statement limitation you can use
binary switching logic. For example, assume the numbers 1 through 10 can be
placed in cell A1. You need to write an IF statement that includes all 10
numbers. You can write the statement similar to this ie. if A1=1,50 etc.:

=(A1=1)*51+(A1=2)*52+(A1=3)*53+(A1=4)*54+(A1=5)*55 +(A1=6)*56+(A1=7)*57+(A1=8)*58+(A1=9)*59+(A1=10)*6 0

If you need the statement to evaluate multiple conditions as True, use plus
(+) signs instead of multiplication (*) signs. Also, this technique works
for numbers only. The formula can not return text entries.

----
Regards,
John Mansfield
http:www.pdbook.com


"SUB-ZERO" wrote:

Hi

Is there any way to change the number of functions allowed in each cell?
I have a spreadsheet and I need it to handle more functions than 7.
10-15 functions wolud really work well for me.
Here is one example of what im working with, I need to have a few more
allowed functions for the formula to work correctly.
=IF(D11=0,D12,IF(D10=0,SUM(D1112),IF(D9=0,SUM(D1 012),IF(D8=0,SUM(D912),IF(D7=0,SUM(D812),IF( D6=0,SUM(D712),IF(D5=0,SUM(D612),SUM(D512))) )))))

Any help would be greatly appreciated

Thanks ...


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-


  #4   Report Post  
Old January 23rd 05, 10:35 PM
Harlan Grove
 
Posts: n/a
Default

"John Mansfield" wrote...
To get around the "nested 7 levels deep" IF statement limitation you can
use binary switching logic. For example, assume the numbers 1 through 10
can be placed in cell A1. You need to write an IF statement that includes
all 10 numbers. You can write the statement similar to this ie. if
A1=1,50 etc.:

=(A1=1)*51+(A1=2)*52+(A1=3)*53+(A1=4)*54+(A1=5)*5 5+(A1=6)*56+(A1=7)*57
+(A1=8)*58+(A1=9)*59+(A1=10)*60

....

Bad example. This formula should be rewritten as

=IF(AND(A1=INT(A1),A1=1,A1<=10),A1+50,0)

or more generally as a lookup.

"SUB-ZERO" wrote:
Here is one example of what im working with, I need to have a few more
allowed functions for the formula to work correctly.

=IF(D11=0,D12,IF(D10=0,SUM(D1112),IF(D9=0,SUM( D1012),
IF(D8=0,SUM(D912),IF(D7=0,SUM(D812),IF(D6=0, SUM(D712),
IF(D5=0,SUM(D612),SUM(D512))))))))

....

IF D11 = 0, doesn't SUM(D1112) = D12? I do see that you're trying to limit
the sum, summing only over the numbes in D512 below the bottommost 0. You
could use the following formula instead.

=SUM(IF(COUNTIF(D511,0),INDEX(D,LOOKUP(2,1/(D511=0),
ROW(D511))),D5)12)




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
cell format for financial functions estephens Excel Discussion (Misc queries) 0 January 23rd 05 03:25 AM
Addition to Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 9th 04 02:06 AM
Can I retain functions from a previous cell when inserting a new r Philobr Excel Discussion (Misc queries) 1 December 3rd 04 10:41 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM
VLookup resulting in a blank cell... KempensBoerke Excel Worksheet Functions 1 October 28th 04 09:57 PM


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017