Using Formated Number In Formula
Jul 29, 2009
i need to use a cell which has the value 00:30:00 (formated as hh:mm:ss) displayed as 0.020833333 (formated as general)
what i need to do is use the 0.020833333 for a calculation.
now the problem is that i have 00:30:00 as input.
that means the user is inserting 00:30:00 in a cell. i need to convert this to 0.020833333 and use it in a formula
the conversion itself is easy. (just change formating of the cell)
i tried using it like this:
A1: 00:30:00
somenumber<(TEXT($A$1;"##.#########"))
but that gives me strange numbers
and what i need is this:
somenumber<0.020833333)
View 9 Replies
ADVERTISEMENT
Jan 9, 2008
I am trying to do is have an additional row, or rows entered after after the last available row is filled. I've written a macro that searches for the last data set in a column, and will then copy the row above and insert, then copy the formating and formulas down, but I can only make this work by having the user click on a control button.
I would like to make this macro work automatically when data is enterted into the last row in the quote form. Below is what my current macro looks like:
Sub AddLine()
With ActiveSheet
. Unprotect Password:="*********"
ActiveSheet. Range("m17").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.FillDown
ActiveCell.Offset(-1, 0).Select
Selection.EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(1, 0).Select
.Protect Password:="*********"
End With
End Sub
View 9 Replies
View Related
Apr 26, 2006
I want to concatenate several cells, some of which are formated as fractions,
and have it display those numbers as a fraction. When I perform the
function, it displays the cell in its decimal form.
Example:
=CONCATENATE(E2," ( ",F2," in. "," ",G2," ",H2," in. ", I2," ",J2," in."," )
"," color #")
Where: F2, H2 and J2 are fractions.
Result: 12" Stretcher ( 11.625 in. x 23.625 in. x 3.625 in. ) color #
View 12 Replies
View Related
Jul 20, 2009
For making quotes at work, I'm looking to have the quotes named automatically. The format of naming our quotes is the date (in this format) then quote name: yymmddNAME (Example for today: 090720EXCEL)
I'm using the =NOW() function for the date (Cell A1), then doing a custom format to turn the format into yymmdd then in a different cell I'm putting in the "NAME" (Cell A2) and what I want to do is combine the 2 cells (Cell A3) by doing: =A1 & A2
but when i do this, the format of the date gets messed up.
View 2 Replies
View Related
Apr 1, 2009
I have the foollowing equation in a cell:
=NETWORKDAYS(A2,A12)+G12
My answer is 1081:23:42.
Is there a way to have it show the number of days, hours, minutes and seconds? So it will say 45:1:23:42? (45 days, 1 hour, etc...) Or something along these lines?
View 9 Replies
View Related
Dec 31, 2008
In Column A1:A10 I have a really long series of alpha numberic digits in each cell.
I use this macro with text to column to split them up for me into different columns.
The problem I have is that after they go through this conversion all of the fractions in columns L are turned into dates....
View 9 Replies
View Related
Mar 14, 2014
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost
Col B = The number of the month when the costs are starting (1=Jan etc)
Col C = The duration or the number of months for which the costs are to spread
Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
View 3 Replies
View Related
Apr 27, 2014
I'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.
View 11 Replies
View Related
Aug 25, 2006
Designated Cell = 7
1 10 .034
15 25 .072
35 45 .089
Output Cell = ???
I am trying to find a formula for the output cell. Suppose the designated cell = 7 due to formulas above it. So that number is always given but can change. I then need a formula for the ouput cell that says if the designated cell is greater than or equal to 1, and less than 10, then the output cell should equal .034. So suppose the designated cell = 18.2. Then I would want the output cell to be .072 since it is greater than or equal to 15, and less than 25. The template I am using has 23 rows of numbers like above. I didn't know how to do it for 23 rows though. If/Then doesn't work because it is 23 statements long (well over the seven limit) and the vlookup only works when the cell is equal to a number and not a range.
View 9 Replies
View Related
Sep 28, 2006
I'm trying to use the formula vlookup, but running into trouble. I'd like to use =VLOOKUP(3000&A1,E:F,2,0) but this isn't working correctly because it doesn't recognize "3000&A1" as a number? Is there a way to do this? This will be used in a VBA code, so I prefer the solution in VBA, but either way is fine. See attached for more info.
View 4 Replies
View Related
Jul 22, 2014
Is it possible to write a formula that skips x number of cells and have the formula autoincrement as usual. For an examples lets say I want to skip 3 cells and I have entered in the cells listed:
A1= "1"
A2= "2"
A3= "3"
Then I type in the simple formula in the cell
B1= "=A1"
and then drag down but it skips 3 cells so then
B5= "=A2"
B9= "=A3"
If it didn't skip then
B1= "=A1"
B2= "=A2"
B3= "=A3"
this is just an example as I will be writing more complex formulas and need some thing that I can write into the formula so that it skips.
Something similar to this B1 "=A1(skip3cells)"
View 4 Replies
View Related
Jan 6, 2010
I want to do as the following:
In sheet 1 I have all the data needed for sheet 2, organizing in row, which means for a particular cargo, its details will be all having same row number, different column alphabet.
Cargo A data 1 data 2 data 3 ....... and so on
Cargo B data 1 data 2 data 3 ....... and so on
In sheet 2, I have a form in which I wish to have it auto fill in the cargo particular by inserting in just the row number for the cargo in sheet 1.
Cargo A
data 1 =Sheet1!A#
data 2 =Sheet1!B#
data 3 =Sheet1!C#
"Cargo A is row 2, then # should be substitute with 2"
View 9 Replies
View Related
Apr 16, 2014
I have list of item numbers which some of them contain letter "C" or "S" at the end. For example,
8002
8002C
8002S
85555C
825S
Is there a way to select number only?
View 2 Replies
View Related
Dec 14, 2013
For instance I have a formula such as =sum(A1:A9). But imagine that the range is not always till row 9 but depends on the total number of rows that are in the table. How can I change that 9 so that it takes a number equal to the total rows in the table?
The final formula I want is not that simple one. It is actually
=INDEX($F$1:$F$1047,MATCH(1,INDEX(($G$1:$G$1047=G2)*($L$1:$L$1047<>"47200")*($L$1:$L$1047<>"47700"),0),0)).
I want to change 1047 by the relevant number of rows. Also, I do know how to do it in VBA, but I have a problem with the length of the formula there (not that one, but other bigger than that)
View 5 Replies
View Related
Mar 23, 2014
The formula below works fine except for,E3,F3,G3,H3,I3. They work great if I was on ROW 3 but A2 could take me to ROW6.
=IF(ISERROR(VLOOKUP(A2,Players!B:B,1,FALSE)),"", SUM(IF(Players!E3="y",Players!$T$5,"0")+(IF(Players!F3="y",Players!$U$5,"0"))+(IF(Players!G3="y",Players!$V$5,"0"))+(IF(Players!H3="y",Players!$W$5,"0"))+(IF(Players!I3="y",Players!$X$5,"0"))))
View 5 Replies
View Related
Feb 18, 2007
formula to put in microsoft excel 2003 to increase a number by 10%, so for example, 20 by 10%.
View 10 Replies
View Related
Jul 3, 2007
=IF(OR(Q136<O132,Q136>O132),Q136-O132,O132-Q136)
This does not seem to be working for me. I need to check one number against another and if the one cell is larger then the other subtract it or just the reverse.
View 12 Replies
View Related
Oct 14, 2008
I need to do a vlookup with the array starting on a row determined in another cell, so I've tried concatenating the number into a formula but this doesn't seem to work. Is there something else that I can do?
E2 is 134
=CONCATENATE("=VLOOKUP($B2,$C$", E2+1, ":$F$2571,3,FALSE)")
returns: =VLOOKUP($B2,!$C$135:$F$2571,3,FALSE) but not as a formula, as text and I need the formula...
View 3 Replies
View Related
Feb 11, 2009
I need a simple formula that would save me some annoyance. Basically I have like 20 columns filled with various numbers. At the last column I like to take the last number in that row. However each row "last number" is in different column. So how do right formula that would basically take the last value in that row?
I have illustration here.
column 1 column 2 column 3 column 4 Final Value
ROW1 3 5 7 7
ROW2 2 2
ROW3 1 4 4
ROW4 8 8
What I want to do is write formula in Final value that will take the last number in the row.
View 3 Replies
View Related
May 25, 2009
I play Keno a lot and have what is probably a very simple formula request. If I’ve got for example this result pasted into Excel: 06 12 15 21 23 27 28 31 32 34 48 49 54 56 60 67 68 74 76 77. How would I go about determining if a pair or two numbers had come in?
Say I was looking for 31 and 76, is there some way I can get Excel to recognize the two spot hit then post in response either 1 (in bold) if yes or 0 (not bold) if no the two did not come in together? Note this would not be only one or the other, they both would have had to hit at the same time.
View 3 Replies
View Related
Aug 5, 2009
I needa formular that will subtract a number per day, for example as one day passes you can set the amount of number to be taken away from the total.
View 12 Replies
View Related
Oct 7, 2009
I need a formula to add the number 1 into cell J9 when cell d9 is no longer blank.
I'm sure it's really easy- but I can't figure it out.
View 4 Replies
View Related
Aug 9, 2012
If I want to include the value of cell C4 in a formula, is it possible to keep the formatting ?
Cell C4 = 10,000.50
If I use a formula :="Profit for the Year ="&C4
ResultProfit for the Year =10000.5
View 2 Replies
View Related
Jul 1, 2013
I have a formula that computes the row number in B58. I would like to combine the row # in the formula after M_MAN!A1015 for eg M_MAN!"&B58&" instead of having to type this. I can then use the foormula to copy the balance of the formulas
=-SUM(OFFSET(M_MAN!A1015,0,22),OFFSET(M_MAN!A1015,0,23),OFFSET(M_MAN!A1015,0,24))
Workings  BC57Row #Quarter5810150Spreadsheet FormulasCell
FormulaB58=ROW(HC_MAJOR_EXP)C58=-SUM(OFFSET(M_MAN!A1015,0,22),OFFSET(M_MAN!A1015,0,23),OFFSET(M_MAN!A1015,0,24))
View 2 Replies
View Related
Feb 3, 2009
Is it possible to determine how many times a formula will be used? Here’s my problem.
I have items that are bought, and then altered. So I need a cell to determine how any times my ‘IF’ formula is used. My current formula is:
Formula entered into D3.
=IF(D2>0,D2,””)
Row 2 is the amount and Row 1 are dates.
If I drag the formula to the right, E2 will be equal to 0 so will enter 0. But I need it to say what ever is in D2 3 times. (As this particular item takes 3 weeks to do) So D2 & E2 & F3 will all have whatever was entered in D2.
View 9 Replies
View Related
Jul 18, 2006
I am trying to create a formula (or if VBA is better) where when a name is put into a field the next alpha mumber is created. Have attached example.
View 3 Replies
View Related
Aug 2, 2006
In a cell there would be a formula, an "if" formula and I want to know is there something in Excel that will lock in a true statement. Like lets say in cell "A1" a formula would read =if(b1>1,.001,.002), I want to lock in a number when the statement is true. Cannot be change no more after when it is true.
View 9 Replies
View Related
Sep 24, 2007
If/when I have removed information from a row, can anyone tell me how to remove empty row but leave row numbers intact and following each other in numerical order.
View 5 Replies
View Related
Apr 16, 2008
I have a monthly report on an excel spreadsheet that I must sum two columns from the previous month for every row in the sheet. I wish to take the value from column B and add to the end of the formula in column A. For instance my column A would contain the following: "=1200+6595+2599+275"
Column B would be a single number, i.e. "3200"
I want to be able to click a button and get "=1200+6595+2599+275+3200" in column A and place a "0" in column B for every row on the sheet. I have a pretty good understanding of VBA, but I am still learning the Excel object model.
View 2 Replies
View Related
Oct 4, 2012
I am trying to make a formula which will tell me if A1 is a postcode (a letter and a number e.g CV42 6AQ)
In the A column, it looks like this:
CV42 6AQ
FC45 D4D
West yorkshire
PR42 6RD
Etc.
i want it to identify all the postcodes, and NOT "West Yorkshire" because it does not contain a number.
View 3 Replies
View Related