is possible to create a formula in a cell (A1) that calculates the average from an entire row of cells (B1:Z1) BUT only considers those cells that are formatted as a percentage. The row (1) contains cells that have percentages, dollar values, zeros, and blanks. If, for example, the entire row of say 100 cells has just 4 cells with percentages in them, how to get it to identify those 4 cells and calculate the average?
View 4 RepliesI have this macro that does an autofill in Column A depending on what is in Column B, however the rows are going to increment frequently. Column B has repeating numbers but they are not all even amounts. So column B can 1,1,1,1,2,2,2,2,2,3,3,4,4,4,4,4,4,4,4,5,5, and so on all the way down. The code will identify when the value changes to something new and in Column A will start numbering from 1 to whatever until it recognizes that a value changes in Column B.
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("C:C").Select
Selection.Cut
Columns("A:A").Select
ActiveSheet.Paste
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select..............................
The problem that I have is in bold with the range. I need it to continue until it recognizes the last cell in the column that has a value and stop.
Right now it will work but only until row A6391 for my first sheet however these rows will increase over time. I'm using Office 2007.
I am using the following macro code to reset a table of entered values to zero. The cells are hyperlinked to autoshapes that change colors depending upon the values in the cells. I used the macro recorder to determine the code. My dilemma is this: I have found that the code for my autoshapes only works or recognizes the zeros that I actually typed in during the macro recording (i.e.C46, E46, and G46), it doesnt recognize the zeros that were entered via the autofill (the dragging the handle technique). If I go back and manually type in a zero in say, E54, that autoshape works properly.
My question becomes this, "What is the difference between the zeros that I manually typed in and the ones that were autofilled in". Perhaps I should do this a completely.
Sub RemoveValue()
Range( _
"C46:C66,E46:E66,G46:G66, " _
).Select
Range("U46").Activate
Selection.ClearContents
Range("C46").Select
ActiveCell.FormulaR1C1 = "0"
Range("E46").Select
ActiveCell.FormulaR1C1 = "0"
Range("G46").Select
ActiveCell.FormulaR1C1 = "0"....................
I am trying to format cells in a range that is not a formula. I tried a UDF and CF but would not work properly. Maybe due to a Bug in Excel.
Therefore created the code below:
I put this code in my ThisWorkbook Module.
What I am trying to accomplish is this.
Check the range of cells, if any cell is not a formula color the background Yellow and the foreground Bold RED.
The workbook has about 40 sheets 32 of them are formatted the same they are numbered "01-31" and 1 is "Temp".
This code works fine but the second procedure would not work when this 1 is there.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myCell As Range
Dim myIntersect As Range
Dim myAddresses As String
Dim SheetNamessToSkip As Variant
Dim res As Variant
SheetNamessToSkip = Array("Splash", "Menu", "Diesel", "Gasolina", "Alcool", "Prev Month", "Totals")
myAddresses = "G12:H51,P40:Q44,P48:Q60,Q14:Q17"
Both subs do what I expect them to do, but can not run them together.
I get a compile error when trying to do it this way.
Is it possible to do the following?
if cell A1 = 6 and cell A2 =5.8 (0.2 below A1) colour cell A2 orange
if cell A1 = 6 and cell A2 =5.5 or below (0.5 or more below A1) colour cell A2 red
if cell A1 = 6 and cell A2 =6.2 / 6.5 / 6.8 (0.2/0.5/0.8 above A1) colour cell A2 green
if cell A1 = 6 and cell A2 =7.2 or above (1.2 or above A1) colour cell A2 green
My formula works fine, but I need the first number of the answer displayed with only two decimal places. The formula averages a set of numbers, then adds " of " and a cell reference.
I want the answer displayed as 2.43 of 3. Here is the formula.
=IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123))&" of "& Z9,"") it works perfectly but displays 2.42857142857143 of 3.
The cell is formatted to NUMBER with two decimal places. . Changing the format has no effect, but changing the formula does.
If I use =IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123)) or =AVERAGE(W12:W123) the result is 2.43. When I add the next portion, &" of "& Z9,"") it changes to 2.42857142857143.
I've tried reformatting cells and nothing changes. This is the only cell I'm having problems with. The worksheet seems to be working fine.
I wish to compare 2 sets of data in 2 cells and in the third cell i wish to both display the difference and format in a colour depending on the outcome of the difference. Is this possible? or is there another solution?
e.g cell A1 is 21
cell B1 is 26
cell C1 is the difference and if the difference is greater than say 2 then it would colour red if equal to 1 then yellow and if less than 1 as in 0 -1 etc it would colour green. I have been very successfully using conditional formatting to colour a cell that has the data inputted but cant now do it for the formula cell. The context is pupil progress but that i guess is irrelevant really.
I have a large spreadsheet with a number of columns that are set up for conditional formatting. For example a column of "BUY"'s and "SELL"'s where the "BUY"s are formatted with the default "light green fill, dark green text". I have other columns that are conditionally formatted using a formula. When I base the result on a formula I don't seem to have the option to use the default formats. Is it possible to use the defaults or maybe even how to customize my format to look like the default? It would look a lot better if I had a uniform red/green style throughout the sheet.
View 1 Replies View RelatedI have a workbook with two worksheets. I added a formula to the first worksheet
= COUNTIF(Scorecard!H3,"K"). It works fine when I add another COUNTIF that references another column (baseball fans may realize I'm counting total strikeouts for a batter): =COUNTIF(Scorecard!H3,"K")+COUNTIF(Scorecard!L3,"K") However, when I try to expand this to cover more columns, =COUNTIF(Scorecard!H3,"K")+COUNTIF(Scorecard!L3,"K")+COUNTIF(Scorecard!P3,"K")
Excel automatically changes the format of this cell to "Text" and it shows the formula as text instead of calculating it. What could be wrong here? Excel's documentation is woefully inadequate for cases like this. Is there an undocumented limit on how many times I can add COUNTIFs together?
The formula works on numbers but not on time with format cell : [u]:mm:ss
HTML Code:
8
7
6
11
12
HTML Code:
8:00:00
8:00:00
6:00:00
3:00:00
[Code]....
I have a large spreadsheet that I would like to indicate the location of the cells (Change cell color or text color) which contain formulas. My thought is that conditional formatting would be the easiest way...
View 3 Replies View RelatedI just upgraded to Excel2010 and noticed that if I calculate percentages resulting from other formulas, the result displays as a percentage with 15 decimal places and it ignores the format of the cell. My guess is that since formulas are being used in cells A1,B1, and C1, Excel is ignoring the format. When I hard code the numbers, I obtain the desired result.
Is there a way to change the default of 15 decimal places for formula results involving inputs that use formulas in their own creation? (outside of the Round function in each cell? Within Options?)
The data looks like this:
A1 = formula resulting in 1.76%
B1 = formula resulting in 1.90%
C1 = formula resulting in 1.69%
D1 = C1*B1/A1 resulting in 1.8218147382920100%
The desired result is 1.82%.
I want to create a system based on the value of a cell appearing as a grade in the adjacent cell. The cell could contain any value from -10 to +10.
the criteria would be
value greater than +2 = 3
value between 0 and +2 = 2
value between -2 and zero =1
value below -2 =0
I can get a formula to work for a single instance but not multiple conditions.
i have a set of conditional formatting set up for my workbook, the first is simply that if the result is false the cell is blank(white text,white background). the second if positive result,keeps the text black till 1 year has passed. the problem is the third, i.m trying to allow the user to blank out the cell within the year (white text,white background)if certain criteria are met. the idea is that they change the entry in cell D from T to TX or from M to MX and the formula pick this up, but i keep getting a error saying "your formula contains an error", the formula im using is, =if(or($D5="tx","mx")) then i set the format to (white text,white background). its probably really simple but i,ve been trying to solve this now for around 8 hours and i.m stuck
View 2 Replies View RelatedWell following on from my question earlier, I am still playing with my timesheet/rota I'm developing.
I have a start time and an end time, which is formatted as hh:mm then an hours cell which adds them up for you.
BUT if someone is on a rest day or a holiday day or a sick day etc, I would like them to be able to type that in without the cell that adds the hours up going doo laly!!!!! (######)
So, is there anyway they can type say 'R' and then getting the hours total cell to ignore it because it isn't a time?
I have a time format in one excel sheet, I need a macro or a formula to change it into a specific format, this is to be able to import it to another software.
I have attached a file here you can see an example, this I manually, I want Row D and E into the format in A, I s it possible to make it in a macro or formula, manually it takes a long time.
File Type: csv TimeFormat.csv
I have a long list of dates that are fomatted as 3-12-13 that I want to format under custom date as d-mmm-yy : 12-Mar-13. I tried to do this but it does not change. I tried to change it to a number and that didn't work either.
View 2 Replies View RelatedI would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?
View 5 Replies View RelatedI have a Sheet 1 which derives some values from Sheet 2.
I want to color cells in Sheet 1 which do not have input from Sheet 2, but the formulas in the Sheet 1 cells are blocking ISBLANK conditional formatting.
Is there a way around this?
I need in changing the format of sheet2 to exactly like the following image below.
*there are some codes in sheet2 which I think is the place to change the format.
I have a column in excel which gets populated in the format "2/1/1980" when copied from a different source. I want a macro to run on this column and convert this in the below format. Required format: '02-01-80 (note the ' symbol before 02 which will keep it as text)
Existing ----> Required
2/1/1980 --> '02-01-80
2/1/1970 --> '02-01-70
2/1/1983 --> '02-01-83
I need this macro to run on two columns and replace them with the required format. I can do a find and replace either in the sheet or through hard coding the VBA, but as the date format keeps growing, I would need to do this via code and hence requesting the same.
I am using the formula =IFERROR((D10*0.5)+(E10*0.5),"") but in some field it gives me a percentage that rounds and others is giveing me a 4 digit percentage
View 3 Replies View RelatedSo I am setting up a spread sheet that tracks time users play on a video game. I can check this info online for each users and the site has the play time in d:hh:mm:ss format. So now I have entered the info for the players and I need to create a formula to show how much time was played that week. Example
Cell B2 is time on 2/27 and Cell C2 is time on 3/6 in Cell D2 I need to show time played in between those dates.
B2 = 0:21:27:00
C2 = 1:01:14:00
D2 = ?????
Note: I have the cells Format as Custom and Type is d:hh:mm:ss
I have a question that I've been struggling with for a few days now. I work with excel quite a bit so I'm used to setting up complex formulas, however I have something that has stumped me.
I have one cell setup with the following format for a phone number: (000) 000-0000. Now what I wish to do is mistake proof the cell and receive an error if it doesn't contain 10 digits.
If I type: =if(A1= (000) 000-0000, A1, "Incorrect") Then it will return as False because it's searching for that exact syntax. What I want it to do is have the cell recognize whether or not it contains 10 digits, so that if I mess up and have 9, it will easily be spotted.
Is this even possible?
I have a workbook containing 5 sheets. Sheet1 is my results page and the balance contains employee names and dates training completed(matrix)
On sheet1 I have 4 columns - 1name, 2type of training, 3date completed & 4refresher due. I have created a combobox and used the vlookup formula to populate the cells within the third column but I seem to have a problem with the date format. In the 4th column I have used this formula (=c2+365) this displays a new date one year on, but if an individual hasn't completed training and the cell within c2 is blank it defaults to this - 30/12/1902. If cell c2 is blank I would like d2 to be blank too.
I have a file with two columns
Column A has a list of equipment names, for example: EQ1, EQ2, EQ3, etc.
Column B has serial numbers of equipment from column A.
I need to create a complex conditional formatting, which will check:
If A1=EQ1, and B1"HCC*" then make it red,
OR
if A1=EQ2, and B1"ABC*" then make it red,
OR
if A1=EQ3, and B1OR("CDE*","FGE") then make it red.
And then same thing for A2, B2 cells.
I have thousands of rows in both columns.
I have to check whether selected equipment name corresponds with entered serial name pattern. I don't know how to do it for each row separately.
I thought of something like:
1st condition: =AND(A1="EQ1",LEFT(B1,3)"HCC")
2nd condition: =AND(A1="EQ2",LEFT(B1,3)"ABC")
...
But how do I apply this conditional formatting to each row, so that it will compare A2 with B2, A3 with B3, and so on, instead of comparing A1 with B1 in all of the rows?
In cell A1 is time now. =NOW()
Cell A2 is a date in the future.
I want to calculate remaining time in Months/days/hours/minutes format by subtracting A1 from A2.
******** ******************** ************************************************************************>Microsoft Excel - time left.xls___Running: 11.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD125/09/2008 16:49 212/10/2008 07:30 3? 4Result in A3 to be in months, days, hours and minutes format Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I was wondering if anyone knows if you can set conditional formatting if the cell contains no formula but is not blank.
We use many formulas to calculate cells and we need to the cell to be colored if there is no formula entered. But the cell is not necessarily blank or zero.
How can I underline the formula in a text like so:
My name is Powder
from this formula
="My name is "&D29
I am trying to limit the format of data to all cells in a column to 2 letters followed by 6 digits. The letters and the numbers are not required to be the same, but the person entering can only enter in that format.
View 2 Replies View Related