I am trying to average letter grades in a spresdsheet my formula seems to work, but if any of the columns in the spreadsheet are totally blank I get #DIV/0! in the cell where I should see the average grade. Is there anyway to stop this from happening. This is the formula I am using.
creating a function to turn a percent (0-100) into a letter grade, I am creating a gradebook spreadsheet.
I think VLOOKUP is the way to go here, but I do not understand vlookup at all no matter how many times I try to look at demos or read how-tos.
I created two columns, one with what the grade entails (93-100 , 90-93 and so on) and the next column has A, A-, etc. But I can't get vlookup to work, any suggestions? I am pretty noob when it comes to excel.
I am inserting a formula into a cell using VBA, but the problem is that I don't know the letter value of column "K" (used in the middle of the line); instead, I know it as column number 11. Is there a simple way to convert the column number 11 to the column letter "K"?
I am looking to be able to compare a number with a letter so for example 4c and compare it with another 5c and in that scenario see a difference of 1 but when the two to be compared are 4c and 4a it would see 0.6.
in this excersize
A=0.33333R B=0.33333R C=0.33333R
it is basically sub category of the initial number so an A is better than a B and in tern a B is better than a C
Targeted Working At Residual 4c 4b 0.33333r 5a 4c -0.33333r 4c 5c 1 4c 5a 0.33333r 5c 3b -1.66666
so on and such forth the number and letter can be entered into seperate fields the lettering are only A,B,C but numbers can range from 1-9 i am normally quite ept with excel but this is out of my depth
I need to extract (and then use for SumIfs) only item numbers from the long description. Please see the attached list where item number column shows existing list & next column shows what i want to extract. The exrtacted part if has any trailing or succeeding letters, characters between numbers should stay. for example from "SGA:RV-SVA:PEPPERS/PEPPERONCINI:SV9176001/232034" I need to extract " SV9176001/232034" or from " SPICES:BULK SPICES 7100:9054B" I need to extract " 7100:9054B". Can some one please urgently help me on this.
I need to compare for each trip whether there is a trip with Es and no Es in it and mark every line in column E with tag "Mixed Use". In the data below trip 2 has mixed use. I have attached a sample file.
See attached file. A colleague is downloading rows of data from a website which contains a number field Excel is currently treating as Text after being pasted in. My spreadsheet includes just a sample of the many rows of data however as you can see the VALUE function refuses to convert these text values to numbers. How these might be converted and why the VALUE function refuses to work in this case?
The back story is that from a system we use, when you download data from inside it, Excel treats most of the cells with a "Number Stored As Text" error. I had problems with this and it was just generally annoying so I wrote the below macro that converts each of the cells back to general cells and reinputs the data, essentially reseting the cell and keeping the data. Being that this is a loop, it takes a good while to run if alot of data was downloaded. Any idea's on how to speed it up besides screen updating? Note, other stuff happens in the first macro, but it is just cosmetic non-intensive things. It calls the second macro because I wanted them seperate should I want to just run the second.
Sub CleanData() Application. ScreenUpdating = False ' Find the Right most cell and the Bottom most cell and ' run the macro from A1 to this cell recursively Range("IV1").End(xlToLeft).Select Col = Selection.Column Range("A65536").End(xlUp).Select Row = Selection.Row Range("A2").Select Range("A1", Cells(Row, Col)).Select Application.Run ("FixTextAsNumberError") Application.ScreenUpdating = True ' End Sub..............
Public Sub add_tick_marks() Dim c As Range For Each c In Selection If Not IsError(c.Value) Then If Left(c.Value, 1) <> "'" And c.Value <> "" Then If InStr(c.Value, "E+") > 0 Then c.Value = "'" & c.Formula Else
I use these because I frequently copy billing account numbers that have leading zeros from some proprietary software and work with them in Excel. I need the leading zeros to facilitate VLookUp's. When I paste the data into Excel it truncates the leading zeros. My typical procedure is:
1. Copy a column of values from our proprietary software 2. Paste it in Excel (Ctrl+V) 3. While newly pasted data is still selected I hit Ctrl+1 which brings up the Format Cells form. 4. I double click "Text" 5. I re-paste into Excel (Ctrl+V) 6. I run my macro to add tic marks to the values so the leading zeros never get lost.
The problem occurs when I copy multiple columns of data and paste it in Excel. If one of the columns of data contains numbers or currency then I select that range of values and run the macro to get rid of the tic marks. After running the macro the numbers are still treated as text. When I select a range of those values and right-click on the status bar and select " Sum" there is no value. No big deal, I just highlight them and format the cells as numbers or currency. But after formatting them as numbers or currency, they retain their format as text until I click in the formula bar for each individual cell and hit enter, at which time they take on the number or currency format I have chosen and the sum can then be displayed in the status bar.
How can I force the formatting I chose to take effect on the data immediately without me having to individually "fake change" the data in each cell?
I have text as numbers downloaded to excel sheets from crystal that i need to add together on a summary sheet. I've tried using Value, TRIM and CLEAN functions to no avail. the TYPE function is reading the cell as Text and I have also tried multiplying by 1. Does anybody know how I can get around this ? hopefully I've attached an extract of the file