Rank Formula Not Working Properly
Oct 7, 2009
I have a rank formula that is seemingly erroring out on certain cells - indiscriminately. I have tried and tried to figure this out for myself but this is beyond my knowledge.
Only on some cells is the rank not working and returning a "-" (the error result).
I want the rank to use absolute numbers, so that it will rank according to variance or percentage (regardless of +/-). I am also using another cell (R1) to tell the rank what column to use.
As you can see there are a few entries, both + & -, that are not being included in the rank - for reasons beyond me.
View 8 Replies
ADVERTISEMENT
Nov 15, 2008
I am trying to work out an excel workbook for calculating family recipes. I have gotten most of it figured out but am having a problem with one thing. When the serving size of an item is 8 oz & the recipe calls for 14.5 oz the only way I can get it to work properly is to enter 1.75 in the serving size.
But I would like (actually need) to be able to enter the 14.5 and have it calculate the calories correctly - since sometimes the amount to be added to the recipe may be 15 ounces and the serving size is 8 ounces etc....
View 6 Replies
View Related
Oct 25, 2009
I have 6 categories that need to be ranked 1-6 in order of highest number of occurances. My ranking formula is showing 1-7, missing number 3. I have attached a sample worksheet further showing what I am trying to explain. I need cell A3 to show a ranking of "3". Currently it shows "4". What am I doing wrong??
View 4 Replies
View Related
May 30, 2013
Total amount in Column B should same as shown in Column F
=IF(E3<>"",SUM(OFFSET($C3,ISTEXT(E3),,,1):C3),"")Above formula is calculating always two rows.
View 2 Replies
View Related
Apr 10, 2009
I have problem with vlookup. I am attaching my file.
View 3 Replies
View Related
Sep 3, 2013
I am including an attached file for reference.
The file is sorted A-Z, but if you scroll down towards the bottom, you will see that some values are out of order. Both instances of University of Central Florida and University of North Dakota are out of place.
University of Central Florida should be between University of California and University of Colorado.
University of North Dakota should be between University of Colorado and University of Southern Mississippi.
View 1 Replies
View Related
Feb 5, 2009
I have a really strange problem i have two different hard directories o: & u:
i have the following code linked to a command button
chdir "U: foldername foldername foldername"
saveactiveworkbook.saveas range("c9")
View 6 Replies
View Related
May 25, 2006
I try the following selection
Columns("B:AF").Select
Selection.ColumnWidth = 2.3
It is selecting columns a to af. Why would take column a if it is not part of select?
Sub Add_worksheet_month()
Dim Lst() As String
Dim I As Integer
Dim wname As Date
'Dim wmonth As Date
View 4 Replies
View Related
Jan 29, 2009
I have a form with 2 buttons and a frame. Inside the frame I have another button. I get a Type Error Message when I run the code and I don't understand why.
View 3 Replies
View Related
Jun 22, 2009
I have the following two codes on a userform, The first code performs two steps:
The first step is running the second code (MyDate), which is used for checking the entries in the userform textboxes and if there is any wrong entry a message box appears then it exits sub.
However, what happens when it finds any wrong entry is that it displays the msgbox and instead of exiting sub as it is requested in the (MyDate) code, it resumes running and moves to the second step and adds 1 to ComboBox1 ListIndex.
View 4 Replies
View Related
Apr 2, 2007
I am in the process of converting my programs over so as not to use these and I am already seeing a drastic difference in speed.
I have run into a problem that I need help with. I have a spreadsheet similar to the example below. The first column contains a list of college majors and after each major is a row of classes that are required by that major.
A25346 ENG101 MAT115 PSY150
A35678 ENG111 MAT115 SOC101
A23456 ENG101 MAT116 HIS201
The first thing I need to do is search down the first column for the major. Once the major is found I need to search across the row for the class. I am having trouble searching across a single row for the class.
Here is the section of code where I experience the problem
Set rMajor = wsMajor.Cells.Find(What:=Major, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rMajor Is Nothing Then
majorrow = rMajor.Cells.Offset(0, 1).Value............
View 9 Replies
View Related
May 25, 2009
I often have problems with the cells range reference method. For some reason I get an error and can't tell why. Other times it works fine. Is this just an unstable method to use or is this completely wrong? I'm assuming I am using the wrong syntax. It is definetly the range reference causing the problem.
Dim cnt1 as integer, cnt2 as integer,cnt3 as integer
cnt1 = 2
cnt2 = 50
cnt3 = 2
dim myrange as range
set myrange = sheets("sheet1").range(cells(2,cnt1), cells(cnt2,cnt3))
I have no idea why it won't work. I'm basically searching a columner range of cells.
View 2 Replies
View Related
Feb 21, 2013
The code below is supposed to insert a column and rename it. However, when I debug, the code only renames the column, it does split or run the loop. I press debug again, and then code executes as it is intended.
I can't explain why I have to press debug twice for in order for the code to work properly.
Code:
Sub renameColumns()
With Sheets("byPosition")
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
.Range("E1").Value = "Exemption"
.Cells.EntireColumn.AutoFit
[Code] .......
View 2 Replies
View Related
Jul 13, 2006
I've been working on a particular spreadsheet and I've just noticed that
double clicking the format painter will only format one cell. It won't
retain the format to be put into the next selected cell which it should when
its double clicked. The same goes for copy and paste. Normally once you've
copied something you can paste it into a number of cells individually, one
after the other, but after the first paste selection, the paste option is no
longer available. I can't see any settings under options to fix this. What has happened, and how do I fix it? (I'm using Excel 2003 SP1 - is there a SP2?)
View 2 Replies
View Related
Oct 12, 2012
I thought I had been able to use array to have more than two criterias with Autofilter, but now I am unable to make the following code work. What I am trying to do is have all the records that does not contain either N/A, S/O or xx and also have a filter on column 125 for the value "OUI".
VB:
Sub test()
Set ws1 = ThisWorkbook.Sheets("SOMMAIRE_EN_ALL")
Set ws2 = Workbooks("Fichier_central_2013_anglais_2_CLEAN").Sheets("DETAIL_CONCAT")
Set r = ws2.Range("A1:du4783")
[Code] .....
View 3 Replies
View Related
Apr 13, 2014
In the attached sheet, one macro is developed which is not working properly. My req is to convert date from dd-mon-yyyy to yyyy/mm/dd format. (23-jan-2010--> 2010/01/23)
If incomplete date is there, then hyphen should place the gap i.e.
For date like APR-2014------> Output should be 2014/04/--
For date like 1998 -----> Output should be 1998/--/--
The below macro works only for row number 11, 15 and 16.
[Code]....
Attached File : date.xlsm
View 5 Replies
View Related
Jul 1, 2014
I'm using the following code to try to ensure a value is entered into a text box, but when I click over to the next text box skipping the first one entirely, I don't receive any message indicating the previous box is empty. I'm trying to make it so certain fields are required and others are optional. Here's the code I'm currently using:
[Code] .....
View 14 Replies
View Related
Jul 23, 2012
I have entered a custom sort order under 'first key sort order,' but the field is not sorting according to the list.
I'm sure I entered the list correctly, because I actually entered it a long time ago, and it was working for a while. However, today the underlying data was temporarily erased, and the pivot table, when refreshed, basically went blank. I restored the data and refreshed the table, but this field is back to sorting in the wrong order. Refreshing the pivot table and re-choosing the appropriate sort options do not work.
View 1 Replies
View Related
Jan 8, 2013
Had been using this formula for almost 3 years, recently the formula didn't work properly as the range goes down halfway only instead of to the last data in the column. I'm using Excel 2010 now.
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
View 2 Replies
View Related
Jan 9, 2007
I'm trying to have a fixed date entered into column B when data is entered into column D. Here's the formula in column B:
=IF(D3="", IF(B3=";", FixedDate(),B3))
When I enter data into cell D, I get the following result in B:
0-Jan-00
Taking this a step further, even if I do a "control + ;" I still get a value of
0-Jan-00 in the cell but the formula bar shows me "=1/9/2007'. ??
I've gone through all of the options but cannot fix this error. I have two spreadsheets using this formula. The first one worked fine last year but now I have this error in it. I also started to create a new sheet and still have the same error.
View 14 Replies
View Related
Mar 9, 2012
here is some code that I have,
Dim zima As Range
Dim Col As Long
On Error Resume Next
Set zima = Application.InputBox("Select column to paste to", Type:=8)
On Error GoTo 0
If zima Is Nothing Then Exit Sub
[code]....
Where in the formula C[-7], I need this to line up with the column that I selected via the InputBox. If I select column L then the -7 needs to change to +2. OR would there be a better way to write the formula in VBA?
View 7 Replies
View Related
Jul 29, 2008
I recently made a formula of net sales contribution (sales per product category/total sales). For weeks 1.2008 to 30.2008. But I have several product categories. I would like near the value of the formula (not in another cell!) to insert a rank of these contributions. I d love to have the rank inside a parenthesis() near the percentage.
Here is the format
NET SALES CONTRIBUTION PER PRODUCT HIERARCHY 239.200740.200741.2007
COM RETAIL MEDIA0,03%0,73%0,22%
DTM STORAGE MEDIA0,00%0,06%0,00%
Of course I dont know how to do it.
View 9 Replies
View Related
Mar 31, 2009
i rank some thing
stock 25
A B c D
rank style qty
1 st1 10 for d i need go to rank 1 first then qty 10
2 st2 5 then 2 then 3 till 25 (stock ) after it comes 0
5 st3 4
4 st4 3
3 st5 6
it can posible with rank and min formula
View 9 Replies
View Related
Jun 19, 2006
I want to find the rank of a year that falls between the start date and end date. For e.g.
Start date : 1/1/2005
End date : 10/31/2014
Target Date: 12/20/2009
Using year(start date)-year(end date), I get the number of years but how to evaluate the answer in a cell to get the result = 5 (my desired answer as 2005 through 2009 = 5 years).
View 2 Replies
View Related
Jan 6, 2008
I am using the following formula to rank a list of data:
=SUMPRODUCT(--($A$2:$A$140=A2),--($B$2:$B$140=B2),--(M2
View 9 Replies
View Related
Jan 8, 2008
I would like to rank the values without the Zero's. Something like the example below. Where the digits are being ranked and the negatives without ranking the 0's....
View 9 Replies
View Related
Apr 3, 2013
a formula to rank a set of data ? The smallest data will be rank 1 and the highest will have the last rank. this range of data has some spaces in between, that needs to be ignored but should not be deleted. I have attached a spreadsheet stating the case.
View 4 Replies
View Related
Jul 31, 2007
what formula would be used to rank scores if you had the following
name score
participant 1 14
participant 2 27
Participant 3 19
to get this result
Participant 2 27
Participant 3 19
Participant 1 14
View 9 Replies
View Related
Aug 14, 2014
I have attached an excel 2010 spreadsheet to show the issue.
I was shown a formula to rank a series of numbers =RANK(A2,$A$2:$A$19775,0).
However, the ranking figure that get's generated, appears to be counting the cells or something like that.
Ranking Example.xlsx
View 10 Replies
View Related
Mar 12, 2014
I am building a football stat database which I am looking to put in an individual sheet which will search the other 20 sheets, collate the information and then display a top 5 ranking in each area. For example, this is our setup for the data:
Screenshot 2014-03-12 21.56.18.jpg
So what I want to to is have a separate sheet which will have a section for say over 1.5 goals %. The formula would go and retrieve all of the % results for each individual team. Then it would rank the 5 best teams and display the team name in the first cell and the % in the other cell.
View 3 Replies
View Related
