Make One Cell Count Twice In AVG If Certain Text Conditions Apply- 2008
Mar 9, 2009
I am using Excel to tabulate votes for a contest. Judges have given a number to each entry, and but certain judges' opinions need to count twice as much as other judges' opinions based on their qualifications.
I've attached the file to help illustrate what I'm trying to do. Morris's votes need to count twice for all Photography or Web Design entries, and Clark's votes need to count twice for all Graphic Design or Web Design entries.
I know I can do this manually by simply copying the number into a blank cell in another column (like the blank column between Morris and Clark's names), but is there any way to make Excel do this for me?
View 7 Replies
ADVERTISEMENT
Jan 23, 2009
I am trying to count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.
View 2 Replies
View Related
Dec 3, 2013
I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.
View 11 Replies
View Related
Mar 12, 2009
I'm new to using Macros and vba code in Excel and am getting problems in creating dynamic charts. Although I had seen all the posts of condional charts in search forum but no use.
I have an excel sheet with Date Received Column,Volume Unprocessed and the third column is to add 5 working days in the date received.I have to ask three questions.Please find enclosed the attachment.
1)In the attachement ,the excelsheet has 4 rows and chart for these values is displayed in chart1. But when new values are added up,it doesn't show those values in the chart automatically and I think I need macro for this.
2) I want macro to automatically calculate date by adding 5 working days in the date received for the rest of the rows.
3) If the current date is equal to date in the third column then the bar should be in orange colour. If the current date is greater than the date in 3rd column then the bar should be green colour and if its less then bar should be red.
View 11 Replies
View Related
Sep 16, 2008
I need two formulas for my workbook. This is what the formula for Cell B6 needs to accomplish: I need Cell B6 to display what I have entered in Cell A6 but only if Cell A1 is greater than 1. This is what the formula for Cell C6 needs to accomplish: I need Cell C6 to display what I have entered in Cell A6 but only if Cell A1 is greater than 2.
View 2 Replies
View Related
Feb 19, 2014
I have to decide if the final answer will be yes or no based on a combination of 4-5 cell values from a single row.
I have attached the details in the excel sheet.
View 4 Replies
View Related
Jan 22, 2009
I have a worksheet with 3 columns in it. these are entitled "area", "uploaded" and "status". uploaded will be a numerical value and status will either be "awaiting signoff" or "completed" what i need to do is list all of the different areas and add the "uploaded" values together IF the status is completed.
View 4 Replies
View Related
Apr 13, 2007
What formula would I use to count a cell only if it met one of three conditions?
ie only if the cell equalled 1, 2 or 3 but not 4?
View 9 Replies
View Related
Jan 28, 2013
I have an issue log containing (separate) columns with dates, numbers and text.
One column (A) has the status (open/in progress/pending/closed/re-opened)
One column (B) has raise dates
One column (C) has close dates
One column (D) has the type of track (this is text, 3 types)
I want to count the amount of cells that have a close date before date x, given they are open (in Column A), and for a specific track (column D).
After creating a new sheet with a date range in one column encompassing all dates that occur in my column B & C, I tried to come up with a formula to count how many cells, and consequently how many issues, were open (or closed) on a particular date per track and as a whole.
So for instance; count cells in column C if Column C is before date 101112 and Column A is open and column D is "Build".
After trying COUNTIF and SUMPRODUCT the conclusion is I can't get it right.
View 3 Replies
View Related
Jul 19, 2014
I have some text in a cell, which is longer than the width that I am going to make that cell. When I don't wrap the text, it cuts it off at the end of the cell.
Since each column represents a day in a calendar, it would be incorrect to merge the two cells, but I don't want the text hidden, since I need to print the final product.
It is non-numeric text, and i've pasted --> values to remove formula issues.
View 1 Replies
View Related
Nov 17, 2007
see my attached sheet cotaining the following questions. in a day report sheet how should i count request matching the crateria of date and other conditions. in a monthly report a heavy conditional sum calculation which make slower sheets how can i make it faster.
View 2 Replies
View Related
Dec 19, 2009
I'm Working on the next part of my long term project.
I need to count the number of Cells Selected in MacroTest2.xls column BG.
( Selection.count) ?
I need to Open Workbook MacroTest3.csv. It has a Header and only Row 2 filled with data.
(Windows("MacroTest3.csv").Activate)
I need to Pulldown/Copy row 2 a number of times so that the Number of Duplicate Rows Created is Equal to the number of Selected Cells in the Count in MacroTest2.xls.
I have been able to do it based on a Static Number of Selected Cells (3) ( the existing Row + 2 ) by recording the manual event with this code.
Rows("2:2").Select
Selection.AutoFill Destination:=Rows("2:4"), Type:=xlFillCopy
Rows("2:4").Select
I'm trying to understand how to declare the variable and apply it, so that the Number of Rows of Template Data in MacroTest3.csv is Equal to the Number of Selected Cells in MacroTest2.xls column BG.
This is part of a Macro that will then transfer some same selected Range of Cell Data as well. It is Currently working but not with a variable number of selected cells. I can post the whole code if it would help you to see the whole picture.
View 9 Replies
View Related
Dec 13, 2013
apply a count to a sheet which adjusts when filtered. I have attached a sheet, but as you can see the filter is returning the incorrect count as it is including the rows which are not displyed
View 2 Replies
View Related
Sep 4, 2009
I am trying to create a daily task activity sheet. I would like to know, how to make the columns visible and invisible as per the selection from previous column.
For eg:
Column A has a list which has values 'TC creation' , 'TC modification', 'System Test' etc.
Upon selecting TC creation, i need to make a column visible ie 'total tcs created'.
Upon selecting 'TC modification' i would have to make the previous column invisible and make a new column visible for entering no of tcs modified.
Upon selecting 'System test' the other columns should go invisible and make other 3 columns visible for no of tcs executed, no of tcs passed, no of tcs failed etc...
View 12 Replies
View Related
Apr 1, 2006
I need a function that will use a column of text values and test these values
to see if one or more of the values exist in a single cell. If it does I need
the function to return true or false.
Ie. cell A1 contains the text "Jim Smith" the B column contains the test
names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is "jim".
Because Jim is in the cell A1 I would need the function in C1 to return the
value "true". If A1 contained the text "bob smith" then function in C1 would
return the value "false".
View 10 Replies
View Related
Oct 16, 2006
This is to manage which departments (approxiamately 30) within a business need which compulsary training (approximately 11 courses)
Spreadsheet currently reads list of new employees and I want to be able to have "YES" or "No" values under the different courses
Is there a formula/function that i can use (like the IF Formula) to complete the following information;
EG: =IF(OR(A3=H2, A3=H5 etc... ), "YES", "NO"
Column H lists all departments
Column A lists deaprtments
A3 representing the 1st Department needing training
View 8 Replies
View Related
Jan 6, 2014
In column B I will be entering a number and I would like it to automatically enter gallons in column c when i do so. So if i enter 356 in b2 i want it to say gallons in c2.
View 1 Replies
View Related
Feb 18, 2010
i have some code where i need to make the text bold but not sure how to go about it
the formula is
View 3 Replies
View Related
Apr 8, 2013
Is there a way to make the text cursor appear at the start, as opposed to at the end, of a cell when pressing F2 to edit it?
View 3 Replies
View Related
Apr 21, 2013
I am calculating service dates for trucks: Need to make the text in a cell turn red after the 90th day? A
View 3 Replies
View Related
Jan 19, 2014
Any way where is a list of cells (b3:b113) has text entered, or specific text entered, it will enter a static cell in the corresponding "d" column.
I know of CTRL + ; but i don't really know how to get it to work as a macro, can't find a forumla that enters the static date, and i don't want to make a macro to copy + paste special the date when it happens.
So all i should have to do is entered text in column b, then it will add a time stamp, when that text was entered, it doesn't have to update.
Like all i need is this, but instead of the "now()" function, it will have another function that doesn't automatically update?
=IF(ISBLANK(E5)=FALSE,NOW(),"")
View 2 Replies
View Related
Aug 30, 2013
Is there a way to make a cell display the text difference between two cells?
For example of cell E2 reads "Thank Goodness it is Friday" and cell F2 reads "Thank Goodness" can I put a formula in H2 that will show the text that is in E2 but not F2 ( "it is Friday")?
View 1 Replies
View Related
Mar 10, 2014
Cell A is the first name, B is a period, C is the last name, D is an "@" and E is "yahoo.com".
I want to squeeze them all together, in order, without spaces, into cell F to make an email address.
What is the formula ?
View 5 Replies
View Related
Feb 8, 2013
I have this code to copy excel data to a word document:
Code:
Set wrdRange = wrdDoc.Range
With wrdRange
.Collapse Direction:=wdCollapseEnd
.InsertParagraphAfter
xText = Rng.Cells(i + 1, 1).Value
.InsertAfter xText
.Collapse Direction:=wdCollapseEnd
End With
I need to apply a heading style to xText after inserting. xText can contain many words.
View 2 Replies
View Related
May 2, 2014
I have an Excel 2010 spreadsheet that I am using to save several numbers all in the same column. These numbers can range from the several thousands up to billions. The formatting I am using for these numbers is Number (using 1000s separator).
number_forma.png
I am also using the spreadsheet to generate a text string for each of the numbers. I don't want the text string to show the number as it is, I want to shorten the number by only showing the first few digits followed by a "B" for billion, "M" for million, or "K" for thousand. For example, in the text string I want to show 1,600,000 as 1.6B.
In order to shorten the number I use the cell in the column to the right of each number. This cell uses the following custom formatting (which I found by doing a Google search): [>999999999.999]#.0,,,"B";[>999999.999]#,,"M";#,"K";
custom_format.png
As you can see the formatting is quite complex (at least for me it is) but it does what I want it to do.
Here is a screenshot of what the original and custom formatted cells look like:
custom_formatted_cells.png
As you can see the custom formatting works and does exactly what I wan
The next column (after the custom formatted number) is where I put the generated text string for each of the numbers. As I stated above, I want the generated text to use the shortened version of the number (e.g. 1.6B).
To generate the text I use the CONCATENATE function with a reference to the cell containing the shortened number as one of the arguments. For example:
CONCATENATE("SOME STRING ", B1)
Where B1 is the custom formatted cell.
The problem I am running into is, the text that gets generated doesn't show the shortened format of the number, it shows the full number. Here is a screenshot demonstrating what is happening:
concatenate_formula.png
As you can see the generated text is "SOME STRING 1600000000". This is not what I want. I want the generated text to be "SOME STRING 1.6B".
I think I understand what's going on. When the CONCATENATE function references a cell it takes the actual value of the cell and ignores any formatting. (I suppose formatting is just the way you see the data, not how underlying functions receive the data.)
My question is, how can I re-write the CONCATENATE function (or use another function, etc. available to me) to use the formatted version of the cell?
*UPDATE* I have attached my spreadsheet as an attachment to this post (tackyjan_excelforums.xlsx). Please note that it was created and saved using Excel 2010.
View 8 Replies
View Related
Nov 1, 2009
i dont understand why this code is not working. i get run time 1004 application or object defined error. basically i want to count column 11 if there is a value in column 2.
Public Sub offloadDoor()
Dim unassigned As Long
unassigned = 0
For rowvar = 18 To 504
If IsEmpty(Sheet2. Cells(rowvar, 11).Value) = False Then
If IsEmpty(Sheet2.Cells(rowvar, 2).Value) = False Then
unassigned = unassigned + 1
End If
End If
Next
Sheet8.Cells("b3").Value = unassigned
End Sub
View 5 Replies
View Related
Mar 1, 2013
Why can't I apply text functions on strings into the file? For example: can't apply "Text To Columns" delimited by space on "Status Entry Date" column.
View 3 Replies
View Related
Aug 8, 2012
I am not sure if this is possible, but worth a shot. I am using a countif function to count a cells and I want it to count only if it has text, followed by a space then more text. I've been able to count the text only followed by a space, but cant figure in the more text part.
View 2 Replies
View Related
Aug 4, 2005
I have a table in Excel:
The first row is time in years.
The second row is method name,say,"A","B","C".
I want to count the number when the time is less than 5 years AND "A"
method is adopted.
I tried this:
count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work.
how to revise the formula?
In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as
countif(C2:Z2,"<5")
View 9 Replies
View Related
Jan 7, 2009
I'd like to use a sumproduct function to count 2 conditions. I want to add the number of times the number 0 is entered in Column D when a 1 is entered in the same row within Column C next to it.
I'm using the formula below yet its wrong.... it gives the answer of 7 rather than 1 (see data in attached file).
=SUMPRODUCT((C3:C124=1)*(D3:D124=0))
View 2 Replies
View Related
