How To Process Non Numerical Data In Excel
May 18, 2014
how to process this non-numerical. I tried to use formulas but it wasn't fruitful. refer the table and.
Name
English
G
Maths
G
Science
G
History
G
Aggregate
Result
Achievement
[Code] ......
****The aggregate derived from the grade of the subjects , where each grade will be given a pointer and the average pointer for all four subjects will be considered as aggregate :
Pointers for grades
A =1
B=2
C=3
D=4
E=5
This is how the aggregate for Alex should be counted:
English A = 1
Maths C = 3
Science B = 2
History A = 1
*****aggregate = (1+3+2+1)/4 =1.75
The result column be showing Pass/Fail. If the student fail one of the four subjects he is considered as Fail.
**Grade D and E =>Fail
The Achievement Column should be indicating the numbers of As, Bs and Cs
for example: O6= A(2),B(1),C(1)
View 7 Replies
ADVERTISEMENT
Apr 12, 2013
I'm having issues with Excel's 2010 conditional formatting. Seems easy to use, but I'm trying to highlight values based on 2 columns of numerical data. Example:
Column F:
6
6
14
Column L:
3
NA
17
I would like Column L to highlight values that are greater than Column F in green. If they are less than Column L then highlight them in red.
Seems I was able to do this with Excel 2003, but I don't understand the 2010 version.
View 3 Replies
View Related
Jan 9, 2014
I have two spreadsheets. one contains a master list of computer names. the other contains a list of computer names to be removed from the master list (exception list).
I need to go through the master list and remove any computer names that are on the exception list- for example, if 'computer1' is in the exception list, i have to find and remove 'computer1' from the master list.
The exception list is quite long, and I want to automate this process if possible. not sure how to achieve this.
I'm using Excel 2007 Standard.
View 4 Replies
View Related
Jul 31, 2013
I have been given a task where I need to define 3-4 process and present the various formulas etc.. that lay out the calculation methodology. I was looking for some ready to use excel templates that have an index page, hyperlinks to different tabs etc...that I can customize to my needs.
The idea is to be able to build a user friendly excel process document that will have all the data from our various (3-4) reports that are interlinked. It will serve as a reference point for existing employees and new hires.
View 2 Replies
View Related
Feb 12, 2014
I'm currently trying to automate the process of creating Data Validation.
PFA.
I have Grades and Names List.
" GradesList " Range has values "One, Two, Three, Four "
I'm trying to map Names List to GradesList using INDIRECT().
Debug the function DataValidation() to understand the issue better.
View 7 Replies
View Related
Dec 21, 2006
I have a database which contain many rows and columns. I tried using Pivot Tables to pull out the data from sheet 1 to look like the format in sheet 2, but without success.
View 5 Replies
View Related
Apr 21, 2013
I am a teacher and need a column of student names (which will all be different) but also need to assign them a numerical value of 1 to analyze my data. How can I do this?
View 3 Replies
View Related
Aug 23, 2009
I have a two workbooks, one which is a daily schedule and the other is a yearly summary. The worksheet in the yearly summary uses a date in the first column and the daily sheets each have a date on them. I've isolated my data in the daily schedule and I've retrieved the date in its numerical equivilant. I have data that is 6 cells wide by "x" amount deep on any given day. Essentially, I schedule different things to be made and each row has that designation, process, dimensions, quantity, etc.
I want to test the contents of the first cell in the array, copy the pertinent data, switch to the proper worksheet, and paste it into the line where it goes. I believe I need to check a few cells to narrow down exactly which group of cells on which sheet they would be copied to.
I am not trained at all as far as programming goes, but I've been practicing for a few years now. What I am just going to start doing is testing the cells in the rows in the array of data and trying to divide it out that way. This just seems like the long way around.
View 2 Replies
View Related
Nov 28, 2012
From a large set of process data, I would like to copy those instances where there is indeed a measurement available to a different sheet automatically.
The original data looks like this:
Time Temperature
02-Jan-2010 11:41:50 156.1
02-Jan-2010 11:42:50
02-Jan-2010 11:43:50
02-Jan-2010 11:44:50 157.3
02-Jan-2010 11:45:50
02-Jan-2010 11:46:50 156.8
02-Jan-2010 11:47:50 156.4
02-Jan-2010 11:49:50
02-Jan-2010 11:50:50 157.2
The resulting data shoot look like this (in a different sheet):
Time Temperature
02-Jan-2010 11:41:50 156.1
02-Jan-2010 11:44:50 157.3
02-Jan-2010 11:46:50 156.8
02-Jan-2010 11:47:50 156.4
02-Jan-2010 11:50:50 157.2
I have actually been learning quite a few tricks on my own since I started this data analysis project but I believe this requires some experience with macros, which I really don't have.
View 2 Replies
View Related
Mar 24, 2009
My problem is that data from previous applications are "bleeding" into new applications while running my macro.
I am utilizing the macro below to paste an application and then to convert the application to upper case in addition to some additional formating changes. After performing the macro I save the newly created document to a different folder. With new applications I repeat the above steps.
I tried to incorporate the clearing of the clipboard in my macro using: Edit=>office clipboard=>clear all. The keys strokes during the " record macro" process do not seem to record in the macro.
I am using Excel 2003, SP2
Sub Process_Application()
'
' Process_Application Macro
' Macro recorded 3/23/2009
'
'
ActiveSheet.Paste
Columns("A:A").Select
View 9 Replies
View Related
Nov 27, 2013
I want to pull 'values' from a website off into column's "A" "B" and "C."
What I am looking for is:
Column A: The current date!
Column B: The closing VIX value of the day!
Column C: The 10-day-moving average value of the VIX for that same day!
All values come from this website: [URL].......
(full link so it doesn't look sketchy; also a link to mainwebsite: [URL] ..... )
What would be the formula for this? Or how would one go about this? And preferably the time to pull these values would be at alterable, but ideal time is hard to choose right now... Need to think about it!!!
View 1 Replies
View Related
Nov 4, 2011
I need to audit files produced by others based on a numerical sampling. For instance, I may need to audit every 5th file for one person, but every 12th file for person 2.
Does XL (2007) have an easier way to select every "Xth" file than numbering the lines manually?
View 4 Replies
View Related
Jul 26, 2009
I have two macros one of which imports the data and the other processes the data. These macros were created at different times and need to be joined into a single macro that will combine their operations – with slight modification to the importing macro. Let me describe what each of the macros does:
PROCESS macro:
Cycles through the CONTROL CELL on the “1” tab using the VALUES TO TEST. For each value to test it copies the values form the output tabs and pastes them into the A+B tab. Then it removes the duplicate rows there.
Option Explicit
'======================================
Sub PROCESS() 'This subroutine is called when clicking on the first button
Dim i As Long, j As Long, k As Long 'Those are the variables that will browse through the whole sheets
Dim l As Integer 'this will be used to browse through the values to test
Dim TheSearch(1 To 2) As Object 'This is an array of objects that will be used for the search
Dim TheRange As Range 'This will be used to flag the range of duplicates in the output
'Searching for the Text string 'VALUES To TEST' in the sheet "1" in order to know where the array of values to be tested start
Set TheSearch(1) = Range(Cells(1, 1), Cells(ActiveSheet.UsedRange.Rows.Count + 10, ActiveSheet.UsedRange.Columns.Count)).Find(What:="VALUES TO TEST", LookIn:=xlValues, Lookat:=xlWhole)
View 9 Replies
View Related
Sep 8, 2006
write code to post Excel data directly to Peachtree using VBA rather than the export/ import process?
View 5 Replies
View Related
Apr 21, 2012
I am using the following formula to find out the No. of "HH" in the range given and I am getting the results simply with numerical value as 2, 3 etc. I want the result should be displayed as 2HH, 3HH depending upon the No. of "HH" available in the given range. The result should be displayed as (the No. added with the text "HH").
=IF(AND(DJ10="HH"),COUNTIF(DF10:DJ10,"HH"),"-")
I use excel 2002.
View 2 Replies
View Related
Mar 25, 2014
Excel 2007. At the bottom of a column of data I'm trying to reference the cell $AG4. There are 340 total columns. Each additional column to the right needs to reference the next cell in column AG. So I'm trying to drag and copy horizontally from $AG4 to $AG340. When I try just the single column it just copies $AG4 into everything to the right. When I have to columns ($AG4 and $AG5) and grab both to try to copy horizontally the numbers increasing it just does $AG4, $AG5, $AG4, $AG5, etc.
View 2 Replies
View Related
Dec 2, 2013
produce graphs/charts based on inequalities monitoring information - gender, nationality, age etc. I'm capturing info from several events, one sheet per event. Had a look at COUNTIF but not sure it's what I want and looks like I'd need sub-sheet for each? Capture.PNG
View 7 Replies
View Related
Dec 8, 2008
This should be easy but after searching "consolidate" all that I could find was multiple sheets and complex questions.
I have 5 data columns that I need consolidated into 1 single column. I've tried the consolidate funtion but it appears to only work with formulas and numerical values (unless I cant find the proper command.) I've also tried paste special but it populates the cells following.
I need 5 cells word values pasted into a single cell
View 12 Replies
View Related
May 18, 2007
if it is possible to create a formula to just extract numerical data. It is formated in text or sometimes general format.
Example
John Doe - 1202939402093
109230920933 - Jane Doe
Is there such a formula that will read the cell, and extract the numerical part of the cell and place it in another cell?
View 9 Replies
View Related
Dec 17, 2013
I have imported data and numbers from a website and would like to sort the data in numerical order. Excel wants to sort the data 1,10,2,20... in that order. I know from past experience with an older version there was a way to convert the numbers so they can be sorted in the way that I want.
View 2 Replies
View Related
Jan 28, 2009
I need to create a table of non numerical data that I can refer to in a formula, which will compare two pieces of data (both also inside this table), and based upon which is "higher up in the hierarchy" - the cell will display a certain result.
Basically it will be an IF function using non numerical data that has a numerical value / "rank" attached.
Specifically - I need to allocate values to this list in cells A1:A5...
EUR
GBP
AUD
NZD
USD
....where EUR is highest ranked and USD lowest.
Then in column B and C will be listed each of these values but in various combinations. In column D I will build simple IF functions to show the highest ranked in each combination. eg B1=EUR C1=GBP D1=IF(B1>C1,B1,C1)
View 3 Replies
View Related
Feb 8, 2009
Need to sort my data in column A which contains a long list of numerical data. To do this manually using copy and paste would be easy, but not viable because of the large amount of data.
The attached PDF file illustrates the data. I wish to replace each date of dirth entry in column A (one D.O.B. in each cell / row, expressed as YYYYMMDD) with the client identifier number (format 937XXXX) a couple of cells above. And at the same time delete the row containing the client reference (or its contents). The data in column A should retain the same relationship to the data in the other columns (B,C). it may require a short piece of VBA code.
View 3 Replies
View Related
Jul 7, 2006
Need the code to make a graph (bar diagram) based on the only the rows with numerical data in a column, where there is also blanks and NA. The graph I would like to make and the data-structure are shown in the file Graphs.xls. When updating the graph later on the cells with data may have changed (i.e. new rows with data may have been added)
View 4 Replies
View Related
Feb 26, 2013
What would cause a pivot table to turn numerical data into a letter?
I have a field, cost center, that has about 15 different possible values in it that look like this: 141350, 141360, 141370, 141380, etc. When I compile the data into a pivot table and try to sort on pivot table everything looks normal, except that 141350 is returned as 'R'. If I change the value to anything else, it is fine, but as 141350 it's changes from numerical formating to a single letter.
I'm sure something was accidentally set to do that automatically.
View 2 Replies
View Related
Dec 12, 2009
I'd like to find out the formula where numerical valued between 1-2000 equals True and numerical under 0 equals False. Examples are in the attachment.
View 3 Replies
View Related
Jun 10, 2014
I would like to create a chart with stacked columns. Usually, the data on the Y-axis is some kind of numerical data, such as budget. I would like to have the Y-axis have non-numerical data (= nominal categories).
Example :
Take the following data :
category | productname
red berry
red carrot
green cabbage
green apple
The ouput format i want is a chart which shows on the horizontal axis the categories red and green, and stacks in each category the right productnames on top of eachother; preferably each productname should have a different colour and showing data labels (e.g. "1", "green","apple" could be 1 specific data label).
I don't know how to achieve this, because if you display data as "count of productnames", then automatically you get something like a block with value 2, which doesn't show the different productnames inside that block.
Can the desired chart be made with Excel somehow?
View 6 Replies
View Related
Apr 30, 2014
I am quite inexperienced with Excel and I am struggling to understand how should I set up a formula, within the data validation function to achieve the following:
I need to restrict a certain set of cells, for people to be able to input only the following values: "1", "0.5", "A", "H", "0.5A" (or 0.5a), "0.5H" (or 0.5h)
And I would need the data validation tool not to include blank cells.
View 1 Replies
View Related
Jul 6, 2006
loop that can calculate the median of dynamic ranges. I need to run through all columns and for each column find the the ranges (there are more ranges and they are seperated with a blank row) with numerical data and then calculate the median and thereafter continue down to find the next range with numerical data in that row and so on.
View 4 Replies
View Related
Oct 8, 2013
Recently I have been practising writing my own macros in VBA. I am using Excel 2010.
I have been trying to write a macro to look at two columns in a worksheet (say, columns D and E) and to delete the contents (not the formatting, and I don't want to delete the cell itself) of that cell only if it contains a numerical value (e.g. 1, 2, 256 or any other number). If it finds a cell with letters, then it wouldn't delete the contents of the cell. The cells are currently formatted as "currency" cells.
I have been trying all sorts of things and I just can't get it to work. I can write a macro to clear a range easy enough, but I am stuck on getting it to delete the right stuff. I won't embarrass myself by uploading the code that I have been using..
View 7 Replies
View Related
Mar 13, 2009
I am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in.
I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.
View 2 Replies
View Related
