Data Sorting, Min/Max, Selective Calculation Function
Jul 30, 2007
I'm using Excel 2003 with WinXP SP2.
I'm trying to make a spreadsheet to calculate my GPA for my studies. I can get everything to work fine except I want to be able to calculate an adjusted GPA based on a certain value of classes to drop.
The way it would work ideally is that I would be able to search/sort a data set and if it contained "X" number of entries then the "Y" number of lowest marks would be omitted from the AGPA calculation.
I'm having trouble designing a function/formula or scheme to sort the data range, and identify the lowest "Y" number of entries and recalculate the GPA without using them.
View 9 Replies
ADVERTISEMENT
Apr 28, 2009
IS it poss to sort an entire sheet by one column then another, but to exclude certain rows that include certain data.
This is what i mean, I have a sheet where data is entered on a daily basis. this data has tobe kept with in its dates. Each day starts with a start time and ends with an end time, and on occasion a break is included within the data. then between dates there is a blank line.
IS it poss to sort the data and exclude the blank lines and the start, end and breaks within the data, but keep the data within date, so all data for the 12/4/09 stay within this block...?
View 6 Replies
View Related
Nov 3, 2007
i have a column that has some values in it. Most values are #N/A but some are numbers. I am trying to create a function of some other snipet of code that would substract one value from the previous value and have the result next to the first value.
View 14 Replies
View Related
May 12, 2014
I am trying to find a formula that will look at the figures in four columns and then select the lowest for the calculation.
Basically what I am doing is getting venders prices and placing them in the column. The cheapest is then selected.
Showing which vender is used would be a bonus.
View 1 Replies
View Related
Dec 9, 2011
I am trying to copy selective data from one worksheet to another. My intent is to copy data in column F below to another worksheet in Column H. I only need to copy numeric data in Column F. Below is the screen shot of my data set. There are 244 Batches and each batch contain 4 members. In another words, I want to copy data for 4 rows (F2 to F5) then skip three rows (F6 to F8), Then again copy 4 rows (F9 to F12) the skip 3 rows and so on.
Here is the sample data -
ABC DEFGHIJ
1
2JohnAccounts841.5
3JackReceivable842.5
4JimPayable842.7
5JustinClaims843.9
6Next lines of Data contain expenditure for batch 2
[Code] ..........
View 2 Replies
View Related
Jan 5, 2007
We want to transfer row data based on criteria in that row to another sheet. We only want to transfer certain cells in the row. We have a column that is named status that we want to trigger and identify the row for transfer and then its location in the new sheet. We tried vlookup but it would only take the first instance of a specific status in the column and not take subsequent instances. We then tried the index function but could not get it to work correctly. I hope this makes sense. There will be a high volume of data being inputted continuously so I don't think that copy and pasting is a viable solution....
View 9 Replies
View Related
Jan 17, 2013
lately I have promised to program a little macro which looked easy for me to do but on which I have now spend a considerable amount of time without finding the problem why it is not running. I am working under Windows XP with Excel 2010, here is what i was trying to do:
In a worksheet there are two columns G and H. In both columns it shall be possible to select a number of possible entries from a list. For column G this is done via data validation, no problem. Now, the choices which are there in column H shall depend on the entries made in the appropriate row of column G. Column G thus functions as an overarching category. If I choose "Tree" in G, H would give me branches, leaves, apples. If I choose "car" the choices would be wheel, engine, gasoline etc. This shall be done by a Macro:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cat1 As Range
Dim cat2 As Range
Dim x As Integer
Set cat1 = Range("G:G") 'Task Group
Set cat2 = Range("H:H") 'Task Category
[code].....
When running, it gives me Run time error 1004: Method "Range" of object '_Worksheet' failed. Debug highlights the row
Code:
With Range(Cells(ActiveCell.Row, 8))
View 2 Replies
View Related
Jul 21, 2013
How to selectively transpose a row of dates to columns. I'm not sure exactly how to explain this, so below is an example of what the data look like entered into the spreadsheet:
study ID
provider
visit 1
visit 2
visit 3
visit 4
visit 5
[Code]....
I'd like to extract the data into a new table on another worksheet that looks like this:
Date
provider
study id
visit #
7/21/13
Test Name
10001
[Code]...
This is just a quick example, but basically it would continue through all possible visit dates for the first study ID, then move to the next row of data (i.e. the next study ID) and extract the data from the row and transpose it in the appropriate columns moving down...
View 14 Replies
View Related
Oct 8, 2013
I have a database where I would like to plot graphs from data of different periods and different series.
My current method is to go to the data set formulas and changing data range. The tricky part is that I might skip data series because I am selecting the series required for each chart.
How to do plot the different graphs efficiently and quickly.
View 2 Replies
View Related
Aug 23, 2013
I have a spreadsheet listing various pieces of computer hardware, software and mobile accessories, etc with costs attached.
I have created a dropdown list of staff profiles eg a sales representative, finance assistant, IT administrator.
What I want to do is get Excel to pull the data through from the data sheet, to a printable summary output form (something you could also cut and paste from). For example, a sale rep would need to be set up with a laptop, laptop case, mobile phone, etc, so when you select Sales rep from droplist, I want that action to pull all the associated data through to the summary form. Similarly, a Finance assistant would require different kit , perhaps a desktop PC , monitor , etc.
Next to my data I have created columns with "Y" for yes in cells containing kit that match the staff role requirements, but I am not sure of the best way to pull this together.
View 3 Replies
View Related
Jun 8, 2014
I would need only the first row data to be copied into a master file in column A (row A from each file to column A, column B.... etc - basically transpose value into master file)
View 1 Replies
View Related
Apr 1, 2009
I have a spreadsheet of courses required to reach a certification. On this spreadsheet I have listed the number of hours required for each course in one column, and how many hours I have accrued in an adjoining column. Not all the hours will occur at once, so I tend to bound from cell to cell adding hours in small amounts. What I am trying to do is create a macro that will allow me to add to the existing number of hours to the newly accrued hours, without typing over what is already there.
For example…Class 1 requires five hours total, and I have two hours accrued. If I accrue two more hours (for a total of four hours) I want to update cell E2 without going in to this cell manually and changing this number. I would like to enter the additional two hours in a text box or similar function, and have that function update E2. To add to the level of difficulty, there are four levels of class. This means not only do I need to be able to select which class hours need updated, but which level of class. I have attached the spreadsheet I am working with to try to make things a little clearer.
View 2 Replies
View Related
Feb 15, 2008
I have this formula: =IF(ROWS(H$12:H12)
View 9 Replies
View Related
May 7, 2005
I can do least squares fitting utilizing LINEST and a worksheet. (See attachment.)
I would like to do the same calculations entirely within VBA.
I can specify the values as arrays within VBA :
Sub linest2VBA()
'
' linest2VBA Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Dim dXvalSqd(4), dXval(4), dYval(4), dFirstRegStat As Double
Dim i As Integer
Second Question:
Is there a way to get the remaining regression statistics within VBA by referencing only the three arrays: dXvalSqd, dXval, and dYval?
View 3 Replies
View Related
Jul 26, 2006
if i could get a hand creating a commission calculation.. here is what i'm looking for and my brain hurts trying to make it... I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for..
$0 - $10,000 - 60% commission
$10,001 - $15,000 - 65% commission
$15,001+ - 70% commission..
i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is $12,000, i don't know how to have it calculate the first $10,000 at 60% and the last $2,000 at 65%.
ps.. my excel sheet is set up as follows:
Rows a-g (stuff that is irrelivant)
row h, gross fees
row i, commission (in dollars)
View 9 Replies
View Related
Mar 10, 2009
I am looking for assistance for 2 vbcode functions that will provide current:
1) Calculation Mode - whether automatic, manual or semi automatic; and
2) Calculation Status - whether a recalc is required or not.
These functions need to work regardless of the calculation mode and always need to show the current status.I have tried creating volatile functions, but the problem I face is that if the calculation mode is manual, the function's return value is not updated unless I force a recalc which renders the purpose of the exercise useless.
View 3 Replies
View Related
May 11, 2006
I am trying to subtract two dates to find out whether an invoice is 6 months past due (regardless of number of days). I use DATEDIF in my formula and it works fine until now. It seems the function takes number of days into account and won't return the desire result when there are 31 days. I want to find out whether the number of months between two date are greater than or equal to 6 months without considering the number of days. I am attaching a sample worksheet for better explanation. As you can see, October is not working right.
View 5 Replies
View Related
Jul 26, 2006
I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for..
$0 - $10,000 - 60% commission
$10,001 - $15,000 - 65% commission
$15,001+ - 70% commission..
i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is $12,000, i don't know how to have it calculate the first $10,000 at 60% and the last $2,000 at 65%. any help is greatly appreciated..
ps.. my excel sheet is set up as follows:
Rows a-g (stuff that is irrelivant)
row h, gross fees
row i, commission (in dollars)
View 9 Replies
View Related
May 30, 2014
I have a macros set up to sort a group of figures. I would like to add an IF function to weed out certain items that do not fit the criteria and therefore would not be sorted but left at the bottom of the sheet.
View 5 Replies
View Related
Jun 3, 2014
I have quarters of the year. I'm wanting to find a function wherein when I punch in for example year 2012, the cell would give me the sum of the 4Qs. If if change it to 2013, then it would give me the sum of 4Qs for 2013.
See attached sheet : sum from blocks of cells.xlsx
View 3 Replies
View Related
Jan 29, 2010
I have written a custom function to be called from worksheet cells. The function is basically a wrapper function for VLOOKUP on a single table. It makes cell formulas shorter, easier to read and self-documenting. Here's a much simplified version of the function:
View 3 Replies
View Related
Jun 11, 2009
I have a worksheet that contains investment security purchases, including purchase date, purchase amount and maturity date, each in its own row. I need to develop a formula that will sum the purchase amount based upon the term in days (maturity date - purchase date) of the investment. The respective rows do not contain the term in days so the formula will need to calculate the days to determine the aggregating criteria described below.
One cell should contain the aggregate security purchase amount whose term is 1-90 days and another cell should contain the aggregate security purchase amount whose term is over 90 days. It seems that SUMPRODUCT with a condition is the best way to handle this; however, I cannot get the correct syntax of this formula.
View 8 Replies
View Related
Jun 10, 2006
As an example, cell values as follows.
A1 = 1, 2 or 3, B1 = 2.5
Is it possible to say, if A1= 1 then make C1 equal to B1+4, otherwise return the value of 0?
View 6 Replies
View Related
Mar 26, 2009
Combining two equations with MOD function for UPC check digit calculation.
I wanted to combine these if possible,
View 4 Replies
View Related
Feb 13, 2009
I am trying to create a form with dropdowns (see attached file), so that when a particular company is selected in the first drop down, the second and third only display the relevant business units and departments respectively.
The INDIRECT function has confused me and I am not sure how to use it.
View 4 Replies
View Related
Feb 20, 2009
I have a inventory report that has multiple stores. I am trying to add the total of all stores in two columns named "AE" and "ASC". Anytime the store name says "AE" i want to add the totals of that store in the "AE" column. Anytime the store name says "anything except AE" I want the totals to add in the "ASC" column. Hope this makes sense. How do I do this? Do I use conditional formating?
View 9 Replies
View Related
Aug 29, 2013
I have turned off show zeros on a sheet. This is good in general, but for certain cells I would like excel to show zeros if the formula output for that cell is zero.
Background information: I am working with a very large workbook, and the final sheet is my output file. This sheet is then exported to a CSV file and imported to another program in my workflow process. I need most blank cells to remain blank, so that they are interpreted as blank in the output program. Some of my cells are actually zeros which I intentionally inputted as zeros...those should be interpreted as zero and not blank.
View 5 Replies
View Related
Mar 13, 2013
I have a cheque list with Column A for Cheque number, Column B for Date, Column C for Payee Name and Column D for Amount
Cheque number ranges from sequence 1200 to 3600
Is it possible to set up a formula to sum the amount of cheques in column D based on the cheque sequence number of column A . In other words if based on criteria cheque sequence number is
View 4 Replies
View Related
Feb 19, 2007
i have a VLOOKUP formula
=VLOOKUP(W3,CAT_A,3,1)
where CAT_A is a dynamic named range on the CAT sheet, I also have CAT_B thru CAT_F
I would like to make the VLOOKUP formula neater by using INDIRECT and the initial letter from column I as follows
=VLOOKUP(W3,INDIRECT("CAT_" & I3),3,1)
However I keep getting #REF, where have I slipped up
View 9 Replies
View Related
Mar 20, 2014
I have a workbook with several columns, some of which are for internal company use only.
Sometimes we need to send an updated list to business partners, but they don't need all columns.
Is it possible to make another workbook which takes only specific columns, and excludes the stray info at the bottom of the original workbook? See examples.
Alternatively: Would it be simpler to make an extra sheet in the original workbook, and somehow choose "Safe only this sheet to a new document" whenever someone requests the list?
View 1 Replies
View Related
