i really frustrated to my excel file already . every time when i open it and it will appear a error box '' some text formatting may have changed in this file because the maximum number of font was exceeded'' .
View 9 RepliesI hope the title makes sense. I have a spreadsheet that I'm trying to use to calculate volumes of liquid. For each liquid there is a minimum and maximum amount that can be included in a vial, and a checkbox to indicate if it should be included or not. What I'm having trouble with is some logic (other than a million if= statements) to determine the amount of each needed.
If only a few are selected, using the max value for each is fine as it is below the volume of the vial, so I want to use the max. But if enough are selected, the max for each would exceed the volume of the vial, and I want to pick a percentage of each component's range until we get a volume that will fit in the vial (ie, it figures out that 84% of the max for each will fit, but knows never to drop below the min).
I would like to get number of cells since the cell value exceeded a given number in a given range of numbers.
example
cell address cell value
a1 1
a2 1.9
a3 -1.2
a4 4
a5 3
I would like to know number of cells since value was less than 1 : the answer is 3 (a5,a4,a3)
Number of cells since number has exceeded 3: the answer is 2 cells back ie a5,a4
I was hoping that my formula would give me the count number based on the Maximum time (latest time) and the Name field...My result is a 0 instead of 62 (the correct answer).
=SUM((Download!$H$2:$H$10=A4)*(Download!$D$2:$D$10=MAX(IF(Download!$H$2:$H$10=$A4,Download!$D$2:$D$1 0)))*Download!$I$2:$I$10)
Would a Index/Match/MAX function be more efficient?
What does "Option Explicit" mean? What does it do/why is it used?
View 9 Replies View RelatedThe following code was posted in another thread:
Private Sub UserForm_Initialize()
Dim a, v, x, ws As Worksheet
Set ws = Sheets("Scheduled Courses")
a = ws. Range("a2", ws.[a65536].End(xlUp)).Value
With CreateObject("scripting.dictionary")
For Each v In a
If Not IsEmpty(v) And Not .exists(v) Then
.Add v, Nothing
End If
Next
x = .keys
End With
With Me.ComboBox1
.Clear ' clear the listbox content
.List = x
.ListIndex = 0 ' select the first item
End With
End Sub
How are the brackets used in the 4th line? I have not seen this notation before. Is this some kind of a shorthand for a Range("A65536") reference?
I'm looking at some VBA code that I have to convert into another programming language and I'm having a little trouble reading it. Here's the line that's throwing me...
Application. Range("Rx_BlueMoles") = 2 * (1# - Application.Range("Rx_BlueMoisture")) * Application.Cells(i, iColDraw) / Application.Range("Rx_BlueMW")
In particular, what is "1#"?
I am preparing one macro and to make below macro more easier.
My Data is not constant. Everyday it get change.
In my data i am trying to delete all rows which begin with "MEANING".
I have prepared below macro but problem here is in range i am taking maximum rows i.e 65000 as i am not sure how many rows will be there in my data which begin with Meaning.
I just wanted to know what is the coding we use to go down untill data end if we are not sure about the range.
Selection.AutoFilter Field:=1, Criteria1:="=MEANING*", Operator:=xlAnd
Range("A3:A65000").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
difference between these statements:
Quit, Exit , Break , Continue, Return
For example if want to stop code execution what should I use?
I am using Excel 2003 and have a spreadsheet that lists 1521 fonts. I am using VBA and looping through the list of font names in column 1 and want to put an example of the font in column 2. I am trapping for error 1004 and using Resume Next. This works fine for 480 lines and then it just stops working. No error message or anything. The only thing I could think of is there might be a maximum number of fonts allowed per spreadsheete, but I would have though I would get an error.
View 3 Replies View RelatedI tried to Google this question but cannot find the answer. What is the maximum number of worksheets that Excel can hide within a workbook? I have a macro which hides all worksheets with the exception of the active worksheet and it has never failed. But this morning I had started working on a workbook containing many sheets and when I ran my HideSheets macro a maximum of 14 sheets hides every time. Is it my code (see below)?
View 2 Replies View Relatedhow to make vb Scroll Bar “Max” number to be equal to the next empty cell in column A in Sheet25. For example when A5000 cell is empty in Sheet25 then in Property of Scroll Bar Max setting would be 5000 and scroll bar must be at the position of max number.
View 4 Replies View RelatedWhat formula can i use to obtain the address of the cell which contains the highest number? My range is A:A
View 2 Replies View RelatedI've got a table where I want excel to generate the next number. With the following table excel must generate an invoice number (column B). I've got the formula to work correctly until a payment is done as shown in B12,B13 and B14. I need a formula to lookup the maximum invoice number (SC1-0007) and in cell B15 the serial number SC1-0008 must be shown. [URL]
View 1 Replies View RelatedThe content of Cell A1 looks like this attccggttaattcccccaaaattt
(only a,t,g,c -nucleotides). I want to know the max times C occurs in this cell and the position from the start. like that a, t, g.
here the answer is 5 times and distance is 13 from start.
selecting a range under a condition. I'd like to write a macro to find the maximum value for a certain ID. The number of ID rows (left column) is unknown so can't say range("A1:A5"). Then have the ID and max number shifted to another column. Maybe use 'rows.count' ?
Eg.
4009 5
4009 9
4009 8
4009 4
4005 7
4005 3
4005 9
3008 10
3008 11
I've done some searching, but haven't been able to find a reference anywhere to:
what is the maximum number of criteria that autofilter will support?
I have two columns of data with a minimum value in the first column and a maximum value in the second column. What I'm trying to get is an indicator in a third column which tells me if a whole number falls at or between the minimum and maximum values. Here is a sample of my data:
A1 B1 C1
0 0.1 TRUE
0.2 0.5 FALSE
0.6 0.8 FALSE
0.9 1.2 TRUE
1.3 1.6 FALSE
1.7 2.0 TRUE
i'm looking to return the cell address of the max number in a given range?
View 3 Replies View RelatedI have a table and I want to find out the what is max number for Apple sold on Monday.
FruitsQtyDaysApple20SaturdayApple50SundayApple100MondayApple500MondayApple20
MondayMango50MondayBanana640FridayMango30FridayGet MAX ForApples on Monday500
I am dividing the number. I have for example 20 hours(A1) which have to be divided into 8h + the rest.
So, 8(B2)+8(B3)+4(B4) hours. I have tried few things, but I can only divide it by 8 and it doesn't go anywhere from that.
Is there a way I can count the number of empty cells in a range, but up to 1 below a maximum, in this case a cell value?
For example, if the value of A1 is 10, I want to count how many blank cells appear between the range B1:B30 up to a maximum of 9, (1 below 10).
the maximum number of tabs/worksheets 1 workbook hold? I am using Excel 2003.
View 9 Replies View RelatedI have a got a table ( 10x30) with random numerical values.
How can I return a column number with maximum value?
Values will be changed periodically so the maximum number could be in different column each time
Example:
A-B-C-D-E-F-G
1-2-3-4-5-6-7
2-6-8-18-1-1-8
2-5-6-9-12-1-7
The results will be 4.
I need to add more conditional formatting to my spreadsheet. But it seems to belimiting me to 3. Is there a way of increasing it?
My spreadsheet contains a tasklist. It is generally sorted by priority (number); each row also contains a task ID ("T0100"). When I add a new task I need to know the last used task ID so I can assign the next one.
I've tried various built in functions but each expects numbers.
How can I insert in a cell (or display in a msgbox) the maximum text value?
For example, Given the data below I'd like to have show "T0888".
PRI TASKID
0 T0123
0 T0199
0 T0101
1 T0888
1 T0105
I need a formula to give me a MIN or MAX value only if there is a number greater than 0 in the cell AND not alphabetical letters (as some cells do contain). I need it to return the value for that cell across about ten sheets--I have that part figured out (I think). But I keep getting a #Ref error.
View 9 Replies View RelatedIn column A I have a set of Ids that are not unique. In column B I have a set of latlong values for those Ids in column A. In column D I have the unique list of Ids from column A(Removed duplicates from column A & pasted them in column D). In column E, I need the latlong value which has highest frequency for each Id. I tried countif,Frequency and tried mixing them up with various other formulas but it is out of my reach.
View 3 Replies View RelatedBasically in cells b12:b26 (15 cells) I need to enter the type of item (which is limited to 2 items). I made a drop down list for that range which works perfectly, therefore the user can choose either item 1 or item 2 or blank. However, I need to figure out a way so that item 1 can be chosen a maximum of 10 times in the range, and item 2 can only be chosen 5 times in that same range..
I am assuming that you can't add that condition to the drop down list... So I was thinking of adding an extra column duplicating b12:b26 (c12:=b12 etc...)- and entering a data validation there.
I was going to enter the following:
if item 1 is chose : =NOT(COUNT($c$12:$c$26)>10) &
if item 2 is chosen: =NOT(COUNT($c$6:$c$6)>5).
However, for some reason it doesn't work - it doesn't block the entry if I try to enter 11 item 1s or 6 item 2s.
1) What's the maximum limit of IF statements in a formula for MS Excel 2010?
2) I have 8 nested IF statements, but I am having trouble with the False part of the argument. I was able to get the False part to work for the 4th argument (because I simply put a comma and closed it's respective colored parenthesis) but the following 3 I can't seem to get and the following message pops when I hit enter "The formula you typed contains an error."
For the False part of the remaining 3 nested statement which is at the end of the formula I just type:
IF($E$2="Q3"........)))IF($E$2="Q2".....)))IF($E$2="Q1")))
And close it's respective colored IF statement argument. I tried putting a comma at the end of each False part for the arguments but it pops with the message "You've entered too many arguments for this function."