Formula is true if proportion of range is true

Trying for true / yes result if at least 50% of range meets criteria eg 
=IF(AND O2="yes",H2<>"",I2<>"",J2<>"",K2<>"",L2<>"",M2<>"",N2<>""),"yes","no")
where O2 must be yes and input is required for at least 4 out of 7 cells 
between H2 and I2
thank you
0
nussbaum (2)
1/24/2005 1:07:02 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
521 Views

Similar Articles

[PageSpeed] 32

=IF(AND(O2="yes",COUNTA(H2:N2)>3),"True","False")

Vaya con Dios,
Chuck, CABGx3


"nussbaum" <nussbaum@discussions.microsoft.com> wrote in message
news:F7589466-8410-4910-8330-104B148CCD93@microsoft.com...
> Trying for true / yes result if at least 50% of range meets criteria eg
> =IF(AND
O2="yes",H2<>"",I2<>"",J2<>"",K2<>"",L2<>"",M2<>"",N2<>""),"yes","no")
> where O2 must be yes and input is required for at least 4 out of 7 cells
> between H2 and I2
> thank you


0
croberts (1377)
1/24/2005 1:16:30 AM
Try: 

=IF(AND(O2="yes",SUMPRODUCT(--(H2:N2<>""))>=4),"yes","no")

--
Rgds
Max
xl 97
---
GMT+8,  1° 22' N  103° 45' E
xdemechanik <at>yahoo<dot>com
----
"nussbaum" wrote:

> Trying for true / yes result if at least 50% of range meets criteria eg 
> =IF(AND O2="yes",H2<>"",I2<>"",J2<>"",K2<>"",L2<>"",M2<>"",N2<>""),"yes","no")
> where O2 must be yes and input is required for at least 4 out of 7 cells 
> between H2 and I2
> thank you
0
demechanik (4694)
1/24/2005 1:21:01 AM
> Try: 
> 
> =IF(AND(O2="yes",SUMPRODUCT(--(H2:N2<>""))>=4),"yes","no")

Clarification: It's presumed that the range H2:N2 may also contain formulas 
which could evaluate to null: "". Using SUMPRODUCT is one way to handle this 
possibility.

--
Rgds
Max
xl 97
---
GMT+8,  1° 22' N  103° 45' E
xdemechanik <at>yahoo<dot>com
0
demechanik (4694)
1/24/2005 1:41:03 AM
Reply:

Similar Artilces:

Adding a formula to a pivot table
Hi, I have constructed a pivot table that summarises data from 11,000 rows. It works well enough but I have been asked to produce totals of certain data. There are 28 Companies in my 'Data' table, which contain applications for projects. Applications occur more than once but always belong to the same company. I can produce a pivot table to show applications by Company but how do I produce a pivot to count how many times a unique application occurs by Company. The best that I could achieve was to add a formula in the top row '=COUNTIF(B2:B10000,"*")', f...

Formulas not extending in lists
The formulas do not extend to new rows in the list. Have the "extend formulas..." checked in the options menu. Any ideas?? ...

enter a formula
i am trying to enter a formula in a cell, but it is coming up as a value. how do i change it from value to formula. -- Louise Isn't the formula supposed to return a value? Maybe something in the conditional formatting. "Louise" wrote: > i am trying to enter a formula in a cell, but it is coming up as a value. > how do i change it from value to formula. > -- > Louise Precede the formula with an apostrophe Micky "Louise" wrote: > i am trying to enter a formula in a cell, but it is coming up as a value. > how do i chan...

Excel hangs crashes when pasting formulas
I have an opened workbook in Excel 07 with my formulas C thru Z. When I copy and try to paste to a new workbook, it hangs or crashes and the memory usage goes to over 1g. If I wait thru the hang, then it hangs when I try to insert or delete any rows. Went to older machine and Excel 03 today to try and everything worked just fine. Event viewer gives following error ID: 1, Application Name: Microsoft Office Excel, Application Version: 12.0.6524.5003, Microsoft Office Version: 12.0.6425.1000. This session lasted 4959 seconds with 900 seconds of active time. This session ended wit...

converting formula to plain data (number)
Hi...first time I have posted here. I have slightly altered a long, long column of data into a separate column that is slighly modified. So the result in the second column might be something simple like R1C1/2. BUT this new column is now made of FORMULAS as opposed to hard data. When I try to transfer this LONG column of "formulas" as opposed to data to a different worksheet, the program won't work as it doesn't know what the formula is referring to. So with that background my question is: Can I convert the second column I created to plain data? In other words the firs...

formula using cell reference returns no value
Using DDE I'm trying to pull data from an application running on anothe machine. Everything works fine when I type the whole pat \\computer\application|topic!'item'. The item is on the sheet and every item is different and a lot of them. I did manage to get th formula to show up correctly in the cell where I want to display th value. I would like the value to show. Formula bar = "\\computer\application|topic!"'" & (a1) & "'" this is close but I did it at work today. Thank -- Message posted from http://www.ExcelForum.com ...

Please help with a formula
Hello all, I need help with a formula that is similar to vlookup or hlookup. In my Excel file, I have various values in cells from G2 to M2 and in cells from G1 to M1. I am looking for a formula that if any of the values in G2 to M2 is "A", give the value from the cell above. For example, if I2 has a value of "A", the formula will give me the value from cell I1. The problem that I am having is if I want to pick up the value from the cell below (e.g. I3), I can use the Hlookup formula like this "hlookup("A",$G$2:$M$2,2,false), but in my ...

I need formula that will automatically count the filled cells.
I need formula that will automatically count the filled cells. Hi! One way: =SUMPRODUCT(--(LEN(A1:A10)>0)) Biff "Benar_Isais" <Benar_Isais@discussions.microsoft.com> wrote in message news:50BEAA7F-93F3-49BD-868E-FF2024E35992@microsoft.com... >I need formula that will automatically count the filled cells. I think =COUNTA() would make it. ...

New Tag Heuer Formula 1 Mens Watch CAH1110.BA0850 Replica
New Tag Heuer Formula 1 Mens Watch CAH1110.BA0850 Replica, Fake, Cheap, AAA Replica watch New Tag Heuer Formula 1 Mens Watch CAH1110.BA0850 Link : http://www.aaa-replica-watch.com/Tag_Heuer_F1_Mens_Watch_CAH1110_A0850.html Buy the cheapest New Tag Heuer Formula 1 Mens Watch CAH1110.BA0850 in toppest Replica . www.aaa-replica-watch.com helps you to save money! Tag-Heuer-F1-Mens-Watch-CAH1110-A0850 , New Tag Heuer Formula 1 Mens Watch CAH1110.BA0850 , Replia , Cheap , Fake , imitation , TAG Heuer Watches New Tag Heuer Formula 1 Mens Watch CAH1110.BA0850 Information : Brand : TAG He...

Formula for calculating age
Hi, What is a good formula for calcluating someone's current age? I have the date of birth in one column and want to have their current age in the next column. The fomula I am currently using is great if I have the column of their age have decimals instead of a whole number...the decimal shows how far into the year they are...but if I just want a whole number there it rounds it up...so I guess I would need it to round down, eh? In any case, how would I do this? Below is the formula I am currently using: =(NOW()-D6)/365 Thanks! Kathy http://www.cpearson.com/excel/datedif.htm ...

Finding constants in formulas
Hello, I have a big spreadsheet which should be purely formula based however I need to check that there are no constants (i.e. hard coded values) typed into the formulas for example '=sum(a1:b10)+25000'. Is there a way to check each cell reference to be totally sure that there are no hardcodings? vb solution perhaps? I know you can switch to view formulas on the spreadsheet but as there are so many and some are quite long it isnt always easy to spot via the naked eye. Any help much appreciated. Thanks Brian Manchester, England Hi Brian, See reponse in Programming. --- Regar...

a range of numbers?
hello, i have excel 2000. heres a breakdown of what i need. ive already written the formula fo this part. when i enter a number, lets say 600, i want it multiplie by 1.35. the total, 810, then needs to be broken down into 60, 30, an 10%. like i said, i did all that already. now, i need a range of numbers from 600 to 4000. all mulitplied by 1.3 then broken down into 60, 30 and 10%. im stumped how do a range lik that. is it possible? any help is appreciated -- Message posted from http://www.ExcelForum.com || hello, i have excel 2000. || || heres a breakdown of what i need. ive a...

Font in Formula Bar
Greetings... I just started using Microsoft 2003. In Excel, I can't read text that is in the Formula Bar because it is i a weird small font. I looked everywhere to figure out how to change th font in the Formula Bar, but i can't find it anywhere. What font does it use? Is it based on an option in Microsoft or is i based on something to do with the Desktop settings...i've looke everywhere for an answer to this. Thanks for any help. jdkuhndo -- jdkuhndo ----------------------------------------------------------------------- jdkuhndog's Profile: http://www.excelforum.com...

Excel Formula #14
I have a spreadsheet with two worksheets - "Data Entry" and "Summary Data". I need a formula for the following: If any cell in "Data Entry", column "I" has a number that is equal to the number in "Summary Data", column "A", I want the date in "Data Entry" column "J" that corresponds to the number in column "I" to populate "Summary Data" column "B" next to the same number in "Summary Data" column "A". Example: (Data Entry worksheet) (Column I) (Colu...

=(Cell) formula does not work
I accidentally had a circular reference, and it probably hosed the worksheet In Cell J4 is a calculated value that equals 100. In cell J5 is a calculated value that displays as zero In cell J6 I enter the formula =J4 and it displays zero In cell J7 I enter the formula =1-J5 amd it equals 0 If I enter in =(Cell) for a non-calculated value, I get the proper display So according to excell 1 - 0 = 0 or simplifying the mathematics 1 = 0. This is different than the math I learned in the first grade Is there any way to restore sanity to this worksheet? Sorry but I don't believe you are giv...

How do I replace my missing Formula bar in Excel?
Click on View then make sure Formula Bar is selected. Regards JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6261 View this thread: http://www.excelforum.com/showthread.php?threadid=497984 im an ediot, THANKS pinmaster! "pinmaster" wrote: > > Click on View then make sure Formula Bar is selected. > > Regards > JG > > > -- > pinmaster > ------------------------------------------------------------------------ &g...

how do I get the edit button back on the formula bar?
I would like to put the Edit button back on the Formula bar. do you mean you upgraded Excel and the formula bar looks different? I doubt that is configurable I am not sure the edit button did anything anyway? -- Regards, Tom Ogilvy "johnjrider" <johnjrider@discussions.microsoft.com> wrote in message news:542F6DDE-6806-4FCC-B0E1-BA2D2FBC5965@microsoft.com... > I would like to put the Edit button back on the Formula bar. See one suggestion at your posting of 14 minutes ago. Gord Dibben Excel MVP On Fri, 25 Mar 2005 11:39:05 -0800, "johnjrider" <john...

formula help #53
I have a spreadsheet that helps me with fuel tax reporting. I need to "tweak" a formula to get the correct results. =IF(C58=0,"0.000",C58/D58) is my the formula that some how I need to add that if the resulting number is above 7, I need it to use 5.5 as the result. I have other cells that will use the resulting number this cell calculates to see how much fuel tax needs to be collected for each state. Is this possible? Please let me know if this is not clear. TIA from a newbie, Cindy Firstly, you do know your formula =IF(C58=0,"0.000",C58/D58) returns te...

Formula problem #3
What is wrong with this formula? =VLOOKUP(INDIRECT("c"&ROW(2:2)),PullTo!$C$2:$Z$1100,9,FALSE) The lookup is supposed to be from a worksheet called "PullTo" bu instead it is returning the value from the worksheet the formula is in What am I missing? Thanks -- Message posted from http://www.ExcelForum.com Here is one I just tested on a workbook of mine. Worked fine. =VLOOKUP(INDIRECT("A"&ROW(5:5)),yourworksheetname!a5:z20,3,FALSE) This is what I was actually using that might help =VLOOKUP(A5,INDIRECT("yourworksheetname!a5:z20"),3,FALSE) -- ...

Formula help please #7
G'day all, I have numbers in N60 to N68 in O60 I have formula =IF(N60="","",SUM(N60/9)) In N61 I want =IF(N60="","",SUM(N60/8)) IN N62 I want =IF(N60="","",SUM(N60/7)) Etc So basically I want it to minus the last number by 1 each time.. is there an easy way for this? Thanks (I know I can do it manually..... But I would like to know for future reference for a larger scale) Thanks again See other post.... In article <e64k07yrJHA.3444@TK2MSFTNGP04.phx.gbl>, "Keith" <keith@home.com.au> wrote...

Handling errors in formulas (how annoying are they!)
I want to write a formula that returns a 1 if the given text is found in another cell, or "" if it is not. This seemingly simple formula is driving me nutty because of excels wacky error codes. eg. [a1] =if(find("text",b1),1,"") [a2] =if(find("text",b2),1,"") [b1]="here is some text" [b2]="here is some words" This returns [a1] 1 [a2] #VALUE! how do I get it to work properly...? The trouble is FIND returns an error value when the text is not found. You turn this to your advantage and use =IF(ISERROR(FIND("text&quo...

Excel Formulas and Functions: Tax
Hi all, don't know if this is the right place to post this question, o dont know if you will understand what i mean, might be very simple, bu anyway, here goes. I was trying to create a "Pay Calculator" and am having trouble wit the Tax part formula(s). Tax is calculated on value of C10. Exampl is, No tax under $110 made, $1 tax if I make between $110-115, $2 ta if i make between $115-$120, $3 tax if i make between $120-$125 and s on. Just wondering if someone could help me with a solution her hopefully. Please contact me if anymore info is required?. Thanks, Josh llenuts at ...

Formula help--Vlookup?
I need help wrinting a formula for the below. Sheet 1 A1=Style Number B1=Size (text value) C1=part number (formula result) Sheet2 contains the data. There are multiple entries of the style number (A1) on sheet 2. The size is actually part of the TEXT description of that style. I need the formula to give me the part number of that Size in that Style number. Is there a way to write the formula to look for the Style number, then look in that description for the text value and when they match, give me the part number 1 column over? Hi, Could you post an example of your data...

How do I create a formula that displays the tab name in Excel?
See Bob's site http://www.xldynamic.com/source/xld.xlFAQ0002.html -- Regards Ron de Bruin http://www.rondebruin.nl "fazeez01" <fazeez01@discussions.microsoft.com> wrote in message news:2A4DF27F-2BDE-4DE9-8B34-55CA583D36B3@microsoft.com... > You'll need this Excel add-in: http://xcell05.free.fr/downloads/Morefunc.zip After you install it, just use the SHEETNAME() function. Example: =SHEETNAME(1) would call the first tab in your workbook. Hope this helps. -- AlexJ ------------------------------------------------------------------------ AlexJ's Pro...

Array formulas vs Getpivotdata
I think that the GETPIVOTDATA function should be promoted above the use of array formulas. I would appreciate comments from the experts. My major problem with array formulas (and the much more user friendly SUMPRODUCT function which I used later) is that they slow down a workbook a lot as they are computation demanding and are recalculated every time a cell is changed anywhere in the workbook. I am probably the only person still using Windows 98 and a PII with office 2000, so maybe it is not an issue for others! I have managed to get the same result using a pivottable and the GETPIVOTDATA fu...