# Percentage Occurence Of Specific Word In Range

Apr 29, 2008
I want to count if cells in a specified range =yes then divide the range to get a total percentage of yes cells. I have a data validation list set up with the values: yes, no, n/a. I don't want the total percentage to change when using n/a.

View 9 Replies
ADVERTISEMENT
Mar 6, 2009

I have a column of equipment average utilization percentages. This column is a dynamic name range called "Avg_Util".

Values in this range were created by a calculation that results in a value of "", for equipment which have no utilization as yet, to those with a utilization of anywhere between 16% to 160% for those with actual hours in service.

I need to count how many have a utilization of <=50% and those that have a utilization of >=150%.

COUNTIF didn't work because it apparently counts the "" values (as zeros?)? I didn't fare much better with SUMPRODUCT either, but that could just as easily be the fact that I'm still new to that function. I get the feeling I'm overlooking something regarding working with percentages here to be having so much difficulty with something that just doesn't seem like it ought to be that hard.

View 5 Replies
View Related
Aug 4, 2009

I am trying to create a formula that shows a percentage of cells within a range that contain a particular number. This percentage is based on another cell, which is the total number of all audits. Each audit has rating columns, with a numeric value indicating it's rating. I need to count those cells within the range that equal the numeric value assigned to the rating, and then divide that total by the total number of audits to obtain a percentage of calls with each particular rating in each category.

Here is an example of what I'm trying to accomplish:

A1 = 2 (Total audits)

B1 = 07/31/2009 (Date of first audit)

C1 = 3 (Rating of first category)

D1 = 1 (Rating of second category)

E1 = 2 (Rating of third category)

F1 = 08/03/2009 (Date of second audit)

G1 = 0 (Rating of first category)

H1 = 2 (Rating of second category)

I1 = 2 (Rating of third category)

The percentage of audits within the first category that have a rating of 3 would be 50%.

The percentage of audits within the second category that have a rating of 3 would be 0%.

The percentage of audits within the third category that have a rating of 2 would be 100%.

View 9 Replies
View Related
Jun 11, 2008

I have one column with many numbers. Some have one dash and some have two.

Example:

123-123456-65

012-789546-1

98B12354-889

Is there a way that I can remove all characters after the last – (dash) in the number?

Example:

If number is 123-123456-65

Then 123-123456

If number is 98B12354-889

Then 98B12354

If someone could just lead me in a direction, I might be able to figure it out. However, my code is elementary and most of the time, I record macros and the play with the code until it does what I want.

View 3 Replies
View Related
Apr 29, 2008

I am attempting an If Or formula and keep retuning a #Value error, I can't for the life of me figure out why.

This is the formula that I have written.

=IF(OR(H44,H45,H46,H47,H48)="Other:","True","False")

Cells H44,I44,J44 have been merged into one cell (H44), and the same for 45,46,47,and 48 - Could this affect the Formula?

View 4 Replies
View Related
Jun 6, 2008

the active sheet contains a number of defined named ranges. i would like to delete range names beginning with "Sales", yet the number of defined ranges changed from sheet to sheet = for example, Sheet1 may have 3 defined ranges (Sales1, Sales2, Sales 3), Sheet 2 may have 1 (Sales 1). Is it possible to included a wildcard search in the following code?

Sub DeleteRangeNames()

Dim RangeName As Name

For Each RangeName In Names

ActiveSheet.Names(RangeName.Name).Delete

Next RangeName

End Sub

View 3 Replies
View Related
Nov 14, 2008

I need to find to the occurrence/count of a cell value in a column/range.

Like say column A has following values

A12

A12

A12

A12

A13

A13

A14

A14 and so on ..

I need to find the no. of times or count A12 is there in the column A.

I have tried using .count function in the range A but this instead gives the number of cells in complete range A

View 3 Replies
View Related
Jul 18, 2006

I am trying to count the occurences of combinations within a range defined by contiguous cell values in one column. My problem lies with setting the value of variables that are queried within the defined range. My macro should;

1)Set ComboValue1 & ComboValue2 values to A1 & B1 cells values respectively

2)define range to be searched by how many contiguous values there are in column D

3)search column E for 2 figure combination (defined by A1 & B1). If present increment counter by 1 and add to column C (to be visible in worksheet). If no match carry on

4)define new range by next set of contiguous values in column C

5) step 3

6)when all possible contiguous ranges defined and searched from column c, perform again on next combination down columns A & B with refreshed occurence counter

7)finish when all combinations are searched for

Sub CountComboOccurence()

Dim ListCounter As String

Dim ProgCounter As String

Dim Counter As Integer

Dim ComboValue1 As String

Dim ComboValue2 As String

Dim Rng As Range

Application. ScreenUpdating = False

ListCounter = 0

ProgCounter = 0

Counter = 0

ComboValue1 = 0.......................

View 2 Replies
View Related
Mar 14, 2014

I would like to have a formula find a specific word in column L and return the sum from column E for the same fund from column C.

I tried using =SUMIF(L:L,"*annual*",E:E) but that gives me the sum of annual for the entire column. I need to be able to specify the fund.

word "annual" also appears in "semi annual" so I need to be able to separate the two.

View 5 Replies
View Related
Mar 24, 2012

In cell B77 i have a percentage formulated from other information in the sheet. I want cell B78 to show a word based on the percentage score.

As follows:

0%-25%- Poor

25%-50% - Competent

50%-75% - Professional

75%-100% - Expert

View 4 Replies
View Related
Jan 14, 2009

I have a UDF for a lookup_occurrence formula (thanks to Dave), and I was wondering if it was possible to tweak the range it looks at with if statements.

I have a formula like this =Lookup_Occurence(B13, INDIRECT($N$5), 1,1,5) where N5 says exit card 1.

I need it to only look at a certain rows within exit card 1 depending on the value entered in I5.

If value I5 says Period_1 it will only look in rows 12:46.

If value I5 says Period_2 it will only look in rows 48:82 etc.

I believe I need to modify the xl look part of the code with if statements.

Below is the code. I am wondering if I can replace the xlLook line with nested if statements. If I5="Period_1 then rows 12:46" etc.

Function Lookup_Occurence(To_find, Table_array As Range, _

Look_in_col As Long, Offset_col, Occurrence As Long, _

Optional Case_sensitive As Boolean, Optional Part_cell_match As Boolean)

Dim lLoop As Long

Dim rFound As Range

Dim xlLook As XlLookAt

Dim lOcCheck As Long

View 5 Replies
View Related
Apr 21, 2006

I am trying to run a macro once a particular word shows up in the combo box. now in my chase i could try to use VBA code to run the macros such that once the arrow is used in the combobox to select a certain word a particular macro labled the same name as the word chosen would run.

View 5 Replies
View Related
Jun 18, 2014

I have a row (will always be row 3) where each cell contains a day of the week, the days repeats for a year or so, making the row almost 400 cells.

Like this,

Mo - Tu - We - Th - Fr - Sa - Su - Mo - Tu - We - Th - Fr - Sa - Su - and so on...

Though, A3 doesn't have to be "Mo" because the days in this case can change (A3 can start with "Tu"), hence I think I need a macro.

So if this row contains a weekend, "Sa or "Su" I want all the cells in the column beneath that which contains a specific value to be cleared.

Example, if "Sa" or "Su" has 3 values in the columns under them, all the values that contain "X" or "Y" has to be cleared.

Like this:

Rows (1,2,3...,)

1----

2----

3 Mo - Tu - We - Th - Fr - Sa - Su - .. and so on..

4 A --- B --- X --- Y --- X --- B --- Y

5 A --- B --- X --- Y --- X --- X --- X

6 A --- B --- X --- Y - --X --- Y --- C

After the macro it should be:

1----

2----

3 Mo - Tu - We - Th - Fr - Sa - Su

4 A --- B --- X --- Y --- X --- B ---

5 A --- B --- X --- Y --- X --- ---

6 A --- B --- X --- Y - --X --- --- C

Notice the two examples in the excel file.

Excelforum.xlsxâ€Ž

View 6 Replies
View Related
Feb 16, 2012

I need to count how many times the word Test is in the range B4:H9 with

Range N2 = Test the formula below works if Test is only in the cell once.

=COUNTIF($B$4:$H$9,"*" & N2 & "*")

But I have data in cells like below, this is all in one cell, so how would I have it count all the times test is in the range when some cells have test 2 or more times in a single cell?

5

Test

8am-2pm

Test

5pm-10pm

View 5 Replies
View Related
Oct 20, 2007

I have attached an excel sheet and I want to count and report frequencies the amount of X that are occurring from the 'CSR1' book in a table in the 'Frequency' book.

The CSR1 book has 4 sections and I need to formula to adjust the total percentage accordingly if only 1, 2, 3 or 4 sections have been populated with data.

Please see attached.testdata.xls

View 8 Replies
View Related
Aug 24, 2014

If we have marks for students in some grade in all subjects, in each subject if we make the marks bounded in (50<, 50-59, 60-69,70-79,80-89 & 90-100).

How we can find the percentage for each boundary?

View 5 Replies
View Related
Jun 9, 2014

how to deduct a percentage off of a range of units.

For example, if I have 100 units that cost $50, I'd like to deduct 20% off of the first 10 units, and then have the total dollar amount put into a separate cell.

So, 100 x $50 = $5000

With 20% off of the first 10 units: $4900.

__________________________

20% of $50 = $10

$10 x 10 units = $100

$5000 - $100 = $4900

View 1 Replies
View Related
Jan 2, 2014

What formula I should use for this condition:

A (Contains many months, e.g. 3 November, 2 December, 10 January, etc. -- in excel date standard format mm/dd/yy)

B (Contains Paid, On process & Waiting for Invoice -- using IF formula)

I want to calculate the percentages of November that has been paid from all of A that contains November in it in column C.

What formula i should use?

I already use =SUMPRODUCT for counting the November.

I only want to use 1 other column to calculate the percentage (C column)

View 4 Replies
View Related
Apr 5, 2014

I have a daily report that gives a percentage based on what is typed into the "Supplier" and "Total Sales" columns but I'm having trouble writing the following formulas since my excel knowledge is relatively low.

1. Where a cell in the "Supplier" column says anything but "Den", "Burrowed" or "Studio Nyx" I need the "60%" and "40%" columns filled with the relevant formula (=B11*60% for example) and the "100%" column blank.

2. Additionally, if the cell contains "Den", "Burrowed" or "Studio Nyx" I need the "100%" column filled and the "60%" and "40%" blank.

View 6 Replies
View Related
Jan 18, 2014

I am setting up a charity accounts sheet, that will also calculate money raised from Raffles, so each raffle will have a name, I can do this by matching the amount with a description but wondered if I could calculate it by looking for specific words or reference in a cell

Sum all cells that the adjacent cell contains the reference "Raffle 0001" but not "Raffle 0002" this needs to be calculated on another cell

Sum all cells that the adjacent cell contains the reference "Rescue" this cell could contain other text as well, so could say "Rescue for Sweetie by Laura Herarty" or "Rescue for Bliss by Jane Telford" all need to be calculated.

View 4 Replies
View Related
Jan 15, 2010

I have a UDF which basically accomplishes a lookup. To do that I use this

Function name(number As String)

Select Case number

Case Is = "1"

name = "BOB"

.

.

.

View 9 Replies
View Related
Aug 28, 2012

Is there a formula to find a specific word through many columns? Between A1 and L4500 i got a lot of diffrent information. I want to show if there is any cells in the row with the specific text, a formula in column M. Etc. If C100 got "topside" a notice in column M

View 5 Replies
View Related
Jan 16, 2007

I am trying to copy rows that contain a specific word from one worksheet to another.

View 9 Replies
View Related
Jan 2, 2007

I am trying to find a formula that will give me a word count in specific cells.

View 14 Replies
View Related
Oct 23, 2009

I am using the formula below to calculate the number of days left to do something, when there are no days left I want the cell to display "Begun" in red italic text. Is it possible to do that via this formula or is a macro needed? =IF(ISBLANK(E2),"",IF((E2-I2)<0,"",E2-I2-10&(" Days")))

View 3 Replies
View Related
Sep 9, 2013

For example, on cell A1 I have 123456 Total, how do I delete the word "Total"? I have abut 2000 lines. What formula do I use?

View 5 Replies
View Related
Mar 4, 2014

I am trying to use a count if function for a specific word and a time range or number. so On Sheet 1 (called Raw Data) I want to check for the word annuity in Column H -

If it is present I want the formula to then check if the corresponding time in column C is within a specific hour.

HTML Code:Â

=COUNTIFS('Raw Data'!H:H,"Annuity",'Raw Data'!C:C, rng,">=19.00",'Raw Data'!C:C rng,"<20.00")

This is what I have so far.

View 2 Replies
View Related
Feb 12, 2009

I want to delete a specific words from string but i have a problem with the code below. For example, i wan to delete the word "Inc" only but the problem with my code is that it is deleting from "Incorporated" too and i want only the code to delete only if it finds the word "Inc" only.

View 11 Replies
View Related
Oct 26, 2009

I need a vba code that will find all the columns (j) which there cell(1,j) includes the word "Date". the word "date" will be a part of a title, for exmple: "due date", "starting date"

View 4 Replies
View Related
Apr 14, 2012

I am in need of a forumula. I am trying to extract one word before a keyword, one word after a keyword, 2 words before a keyword, and 2 words after a keyword.

For example:

The keyword is blue:

birds are blue today - 1 word before=are; 1 word after=today; 2 words before=birds are; 2 words after=today

blue is my favorite color - 1 word before=(null); 1 word after=is; 2 words before=(null); 2 words after=is my

see blue colors in the sky - 1 word before=see; 1 word after=colors; 2 words before=see; 2 words after=colors in

View 5 Replies
View Related