MrExcel.com - Your One Stop for Excel Tips & Solutions

Formula to Long

Posted by Mike Kleimeyer on February 06, 2001 7:46 AM

I need some help to simplify this formula:
=(IF(G8>=1,1,0)+IF(G9>=1,1,0)+IF(G10>=1,1,0) + IF(G11>=1,1,0)+IF(G12>=1,1,0)+IF(G13>=1,1,0)+IF(G14>=1,1,0)+ IF(G15>=1,1,0)+IF(G16>=1,1,0)+IF(G17>=1,1,0) + IF(G18>=1,1,0)+IF(G19>=1,1,0)+IF(G20>=1,1,0)+IF(G21>=1,1,0)+ IF(G22>=1,1,0)+IF(G23>=1,1,0)+IF(G24>=1,1,0) + IF(G25>=1,1,0)+IF(G26>=1,1,0)+IF(G27>=1,1,0)+IF(G28>=1,1,0)+ IF(G29>=1,1,0)+IF(G30>=1,1,0)+IF(G31>=1,1,0) + IF(G32>=1,1,0)+IF(G33>=1,1,0)+IF(G34>=1,1,0)+IF(G35>=1,1,0)+ IF(G36>=1,1,0)+IF(G37>=1,1,0)+IF(G38>=1,1,0) + IF(G39>=1,1,0)+IF(G40>=1,1,0)+IF(G41>=1,1,0)+IF(G42>=1,1,0)+ IF(G43>=1,1,0)+IF(G44>=1,1,0)+IF(G45>=1,1,0) + IF(G46>=1,1,0)+IF(G46>=1,1,0)+IF(G47>=1,1,0)+IF(G48>=1,1,0)+ IF(G49>=1,1,0)+IF(G50>=1,1,0)+IF(G51>=1,1,0) + IF(G52>=1,1,0))/(IF(G8>0,1,0)+IF(G9>0,1,0)+IF(G10>0,1,0)+ IF(G11>0,1,0)+IF(G12>0,1,0)+IF(G13>0,1,0) + IF(G14>0,1,0)+IF(G15>0,1,0)+IF(G16>0,1,0)+IF(G17>0,1,0)+ IF(G18>0,1,0)+IF(G19>0,1,0)+IF(G20>0,1,0) + IF(G24>0,1,0)+IF(G21>0,1,0)+IF(G22>0,1,0)+IF(G23>0,1,0)+ IF(G25>0,1,0)+IF(G26>0,1,0)+IF(G27>0,1,0) + IF(G28>0,1,0)+IF(G29>0,1,0)+IF(G30>0,1,0)+IF(G31>0,1,0)+ IF(G32>0,1,0)+IF(G33>0,1,0)+IF(G34>0,1,0) + IF(G35>0,1,0)+IF(G36>0,1,0)+IF(G37>0,1,0)+IF(G38>0,1,0)+ IF(G39>0,1,0)+IF(G40>0,1,0)+IF(G41>0,1,0) + IF(G42>0,1,0)+IF(G43>0,1,0)+IF(G44>0,1,0)+IF(G45>0,1,0)+ IF(G46>0,1,0)+IF(G47>0,1,0)+IF(G48>0,1,0) + IF(G49>0,1,0)+IF(G50>0,1,0)+IF(G51>0,1,0)+IF(G52>0,1,0))

Posted by cpod on February 06, 2001 8:36 AM

Try this:

=SUM(N(G8:G52=1))/SUM(N(G8:G52>0))

This is an array function and must be entered using Control+Shift+Enter

Posted by Dave Hawley on February 06, 2001 8:44 AM

GOOD GRIEF!!!!


I think will do the same, but i was going dizzy looking at the MEGA! formula

=COUNTIF(G8:G58,">=1")/COUNTIF(G8:G58,">0")


Dave


OzGrid Business Applications

Posted by JAF on February 06, 2001 8:45 AM

Some more information please.

With my interpretation of the criteria you've specified (SUM of: for each cell in range G8:G52, if cell value is greater than or equal to 1 then return 1 else return zero) and (SUM of: for each cell in range G8:G52, if cell value is greater than zero then return 1 else return zero) - this will always give the same total for each "set" of numbers.

As dividing a number by itself results in 1, I presume that you're criteria are wrong.

Can you please repost with a description of the criteria rather than the formula. I'm guessing that one lot of the "greater than's" should be "less than's"???


JAF

Posted by Aladin Akyurek on February 06, 2001 9:49 AM

Dave: You must have gone dizzy. :-) Your formula will end up nothing but in either 1 or #DIV/0! .

Aladin

Posted by Dave Hawley on February 06, 2001 9:52 AM


Aladin

LOL!

quite right! But that is exactly what the MEGA IF does.

My head hurts :-)


Dave

OzGrid Business Applications

Posted by Scott R on February 06, 2001 10:00 AM

You'll get answers other than 1 or #DIV/0! if some (not all) of the data is between 0 and 1.

Posted by Aladin Akyurek on February 06, 2001 10:07 AM

cpod: also got dizzy, I suppose. :-)

You get
when each val in g8:g52 is a zero --> #DIV/0!
when each val in g8:g52 is a 1 --> 1
when no val in g8:g52 is 1 --> 0

Aladin

Posted by Scott R on February 06, 2001 10:16 AM

Should have been
=SUM(N(G8:G52>=1))/SUM(N(G8:G52>0)) ctrl+shft+enter