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
528 Views

Similar Articles

[PageSpeed] 48

=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:

Verify range is square
Greetings. I am needing to do MMULT on a variable square range an unknown number of times. I have it working, but I want to be able to verify the range is square. From the below sub, I can not figure out a way to verify that MatrixRange is actually square. Also, if InputBox("Power") is null, it throws an error. I can't figure out how to deal with that either. Thank you. Greg ********************************************************** Sub SquareMatrix() Dim OffsetCount As Integer Dim Power As Long Dim MatrixRange As Range Dim ResultRange A...

Use sheet names in column in COUNTIF formula?
I have the names of my worksheets in Col A. I want to do a COUNTIF for all these sheets in the same range on every sheet. Is there some way I can incorporate the cell with the sheet name into the formula? Something like: Col A Col B Sheet1 =COUNTIF('(ValueofThisSheet!A2)'!B2:B20,"*") Sheet2 Sheet3 And then of course copy it down the column to pick up all the sheet names? Ed Hi Ed, you use the INDIRECT function to do this, like so: =3DCOUNTIF(INDIRECT("'"&A2&"'!B2:B20"),"item") Then copy down. Hope this ...

Copying formulas #11
Can't seem to find this tip, probably calling it the wrong name. Want to copy a formula (x/y) from cell to cell, but keep the denominator (y) pointed at the same cell each time. -- DQ1 ------------------------------------------------------------------------ DQ1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26841 View this thread: http://www.excelforum.com/showthread.php?threadid=400847 Put a dollar sign in front of the constituents of the fixed cell. For example, in a simple fractional equation =A2/$B$23 The contents of cell B23 will always be the d...

need formula to search column for a word and return another word
I have a multi-worksheet workbook where the 1st worksheet is an assets summary page and the other worksheets are for tracking individual assets. Each row of the individual asset worksheet represent an asset request. A column on the assets worksheet titled "order status" will have one of three inputs for each request: "Open", "Pending", or "Complete". On the summary worksheet, each row represents an individual asset. There is a column on the summary worksheet titled "Item Status". I need a formula to look at the asset worksheet "...

VERIFY REPORT Question
Hi, Ive a question about reports. I appreciate that to enable you to filter on custom fields from standard crm reports you need to verify the database. Now, does this mean that every time i add in a new column to crm i have to go through and verify each of my reports?? Thanks ! that is correct. until you do this each report will not know about the new field -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Jo Hughes" <email.johughes@gmail.com> wrote in message news:1120558923.595980.19740@z14g2000cwz.googlegroups.com... > Hi, > > Ive a question ...

Access Forms displaying records with mathemical formulas
Hi, I need to create a MS Access database to manage a collection of roughly 5,000 memos. Each memo is essentially a text, anywhere from 50 to 1,000 words, and most memos contain mathematical formulas requiring specific formatting. Currently, the memos are scanned pictures and I have designed an MS Access form enabling me to view each picture according to where it is located. I would now like to have all my pictures converted into searchable documents (such as MS Word files) that I could easily manipulate and, most importanlty, still view from within an MS Access database. My general high-lev...

can't change formula toolbar height
Whenever I open either a new sheet or an existing worksheet the formula toolbar/namebox is way to high, the fonts inside the namebox are 20 and the font size in the Normal style is 20. I can change the Normal style to 10pt, but it doesn't change the formula height or the font in the namebox. And it doesn't stay changed. When I close the program and restart it the huge fonts are back. Any ideas? You should change the font name/size specified under Tools, Options, General. Then restart Excel. This will change the definition of the default Normal style for _new_ workbooks only...

working with length formula
I am looking at a cells that only contain numbers. (4,5,6, or 7 digits) Any number that is 6 digits or less, leave as is. IF the number is 7 digits, I want to remove the right most number i.e. Tx 1234 result = 1234 12345 result = 12345 123456 result = 123456 1234567 result = 123456 =IF(LEN(C4) =7,LEFT(C4,6)*1,C4) -- Jim Cone Portland, Oregon USA . http://www.contextures.com/excel-sort-addin.html . (editorial review of special sort excel add-in (30 ways to sort) "wabbleknee" <grunge@hobbleknee.com> wrote in message news:j9clt6$apr$1@speranza.aioe.org... >I...

Big challenge!! Formula for count last 10 columns since today!
Hi, I want to count the numbers of cells with a certain value in it, in a range which starts and ends based on the current date. I have this list of attendence: For every person we register if they were at training or game: training are on wednesday and friday, games sometimes on saturday. For each 'appointment' (training/games-) we have a column. For each player we have a row. If they were at training, we put "1", if they weren't but they warned us we put a "v", and if we didn't get any news at all we put nothing in it. 5-08 7-08 12-08 1...

VBA Range Question
In VBA how do I find the first and last row number in a range? I need to perform various calculations on different segments of a range (single column range) and my first problem is to identify the first and last row. Then I'll loop through the rows and perform various calculations. Thanks. Don P.S. Is there a VBA reference somewhere that would help me answer questions like above? Using the VBA help facility doesn't seem to help, probably because of my low expertise in VBA. Look at this: Sub aaa() Dim MyRange As Range Set MyRange = Range("A10:A20") FirstRow = MyRa...

Formula for making a number 3 digits less...
If I have a value, say $1,000,000 and I want to show it as $1,000 (w note at the top its in thousands)... I know i can just divide by 100 to get that result, but is there an actual formula name that doe this... just curious -- Message posted from http://www.ExcelForum.com On Thu, 1 Apr 2004 12:30:41 -0600, johnnyrad <<johnnyrad.141r53@excelforum-nospam.com>> wrote: >If I have a value, say $1,000,000 and I want to show it as $1,000 (we >note at the top its in thousands)... I know i can just divide by 1000 >to get that result, but is there an actual formula name that doe...

Formula for changing a cell format
How do I change a cell format if certain text is displayed in a nother cell? e.g; cells A4 and B4 (will change to a black pattern background) if C4 displays "Test"? Hi - select A4:B4 - goto 'Format - Conditional Format' - enter the formula: =$C4="Test" - choose your format -- Regards Frank Kabel Frankfurt, Germany Confused1 wrote: > How do I change a cell format if certain text is displayed in a > nother cell? > > e.g; cells A4 and B4 (will change to a black pattern background) if C4 > displays "Test"? ...

make formula look at diff sheet
I have this formula that works great if the data is on the same sheet......=SUM(INDIRECT(ADDRESS(2,MATCH(A1,B1:M1,0)+1)&":"&ADDRESS (2,MATCH(A2,B1:M1,0)+1))) I changed it to this on sheet2......=SUM(INDIRECT(ADDRESS(3,MATCH($C $9,Sheet1!B1:M1,0)+1)&":"&ADDRESS(3,MATCH($C$10,Sheet1!B1:M1,0)+1))) And it returns the 2nd row on the sheet the formula is on rather than Sheet1. Any help would be appreciated. Not exactly sure how this bulletin board works. Thanks. Try it like this... =SUM(INDIRECT(ADDRESS(3,MATCH(Sheet1!$C$9,Sheet1!B1:M1,0)+1,,,&...

conversion formula for TV Frame rate's
Hi, I no there's probably a really easy way to do this, as I can work i out in my head without any trouble, but I'm trying to work out formula in Excel that converts lets say 100 frame's into 00:04:0 format. Can it be done? and am I really stupid ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Qboulder, I assume you want a formula for 24 frames per second. Try the following formula: =3DTEXT(INT(B18/1440),"00")&":"&TEXT(INT((B18-I...

need help on macro/VBA programming selecting a range
I would like a macro to allow a user to select a cell in column A on Sheet 2. If the user selects A6 I would like the macro to select the Range (A6:BF6) in Sheet2 and copy that to Sheet1 starting in cell A1. Thank You in advance. Ron Sub Copy_to_Sht1() Range(ActiveCell, Range("BF" & ActiveCell.Row)).Copy _ Destination:=Sheets("Sheet1").Range("A1") End Sub Gord Dibben MS Excel MVP On Thu, 18 Jun 2009 16:58:32 -0500, Ron Berns <ronberns@barrelofunsnacks.com> wrote: > >I would like a macro to allow a user to select a cell in col...

Excel shows formulas, notthe value
I looked in Tools/options and under view I do not have formulas selected. What else might cause this? Text format in the cells with the formulas? Select the cell with formulas, format as general under>format>cells>number do edit>replace find what = replace with = (you replace an equal sign with an equal sign, that usually forces the formulas to calculate) -- Regards, Peo Sjoblom "Sherry" <nowhere@microsoft.com> wrote in message news:urS20ZrsDHA.3532@TK2MSFTNGP11.phx.gbl... > I looked in Tools/options and under view I do not have formulas selected. >...

Cannot paste a block of formulas from one sheet to another
I am using Excel 2003, SP3 I used to never have problems pasting a block of formulas from one spreadsheet to another (paste special, formulas). Today it is pasting the values from the original spreadsheet instead of the formulas. The spreadsheets are nearly identical except for file name (using batch numbers as file names.). Ideas? Thanks Do you have both workbooks open in the same instance of excel? If you have two instances of excel running, you can see this. Anon wrote: > > I am using Excel 2003, SP3 > > I used to never have problems pasting a block of formulas fr...

Missing formula bar in Excel
I have Office v.X for Mac Just today, I noticed that I can no longer get the formula bar to appear -- even if I try to show it via the View menu and/or to check it in the Preferences menu. I do not know what I may have done to make this happen. I have not been using Excel for several days. I have tried re-installing just the Excel application from my original Office v.X disk (via custom install of Excel only) and it did no good. Can anyone help me find my formual bar? All I can think of is do you have the full screen mode active. Check to see if View > Full Screen has been...

scrollbar with huge range
Hello, I have a sheet where I use at least the first 300 rows, but the scrollbar span an area of something around 60000 rows. I tried the following command: Worksheets(1).ScrollArea = "A1:IV500" but it does not shrink the scrollbar to a reasonable range. What it does is 1. Only the range A1:IV300 can be modified! 2. The scrollarea span an area of A1:IV60000 altought there are no data in the cells! Hope somebody can help me! Thanks in advance! Michael Mike, Try this: Select all the rows following your last data rowTo do this, select the first unused row (click in the r...

Excel
:rolleyes: I have two Excel columns (over 6000 rows) - one ha numerical values and the other has either 0's or 1's. What I need is way to select a range of cells in the first column (col A) depending o when the second column (col B) switches from value 0 to 1. So let' say that the first row of col B contains a 1 (which it always will) and the next 6 rows are all 0's, and the 8th row is a 1. Then th range I need to select in col A is from A1 thru A7. Next, I need t calculate the median of A1 thru A7 and paste that in a third column. My next range selected will then begin w...

Variable in Range
This is my code: Sub Macro1() Dim intRow As Integer intRow = InputBox(Prompt:="Enter Age", Title:="Age") Application.Worksheets("Mold Flg Thk").Rows("10:19").Insert End Sub I would like to use the intRow variable in the .Rows range. But, Application.Worksheets("Mold Flg Thk").Rows("10:intRow").Insert doesn't work and neither do a few other variations. Can the range be defined using the intRow variable? Application.Worksheets("Mold Flg Thk").Rows("10:" & introw).Insert aftamath wrote: > &g...

Formula for adjacent row sums?
Hello. I have a speadsheet that has numerous rows and columns of amounts that need to have an automatic sum entered in the middle of each row. For ex: Inv Date Part Labor Tax Total Other Paid (auto sum) other other 23 1/4 nut 27. 2. 29. -3. 22. ( ) 0. 0. --- Message posted from http://www.ExcelForum.com/ ...

Please help on formula that will not work (If-And formula)
Excel 2007. I cannot seem to get this formula to work: =IF(AND((VLOOKUP(CONCATENATE(TRIM($A29),TRIM($A$27)),'test (083)'!$J:$L,3,FALSE)>=C$28),(VLOOKUP(CONCATENATE(TRIM(A29),TRIM($C$27)),Overrides!$E:$F,2,FALSE)<>"N")),"Y"," ") I am trying to do an if statement to enter a "Y" or blank based on my AND parameter. The first AND logical test is checking to see if the vlookup number is greater than the number in cell C28 which is a number 4. This part of the formula seems to work as I tested it by itself. It is the second ...

Formulas do not update
Excel 2003 - Formulas on one worksheet (pMon) referring to another worksheet (pCSRs) all-of-a-sudden stopped updating when the information on the second worksheet changed. These are simple formulas without calculation, such as =pCSRs!F92. Calculation is set to automatic, and even when I press F9 they don't update. This has worked for a long time, but I have recently been making changes to other worksheets by importing data from another similar workbook. However none of the problem formulas involved have a reference to the other workbook (nothing like ='[Workbook2]pC...

VBA or Formula Needed ????
The vlookup table automatically updates when a the next destignate AF:AH orange background is updated. I now need help with a formula or VBA that will identify from thi table EC,ED,EE,EF; 1. which digit (0-9) in the EC column has the highest value in the E column and place that digit in the next orange row's W cell (for example W436). 2. which digit (0-9) in the EC column has the highest value in the E column and place that digit in the next orange row's X cell (for example X436). 3. which digit (0-9) in the EC column has the highest value in the E column and place that digit in...