I have an Excel document which receives data dump from a weather website cells B1:I20. I need the temperature and dew point from cells B3:I3, however the data is returned in a "Temperature / Dew Point" format. To use the data, I separate the Temp and DP into their own cells B22:I23 by:
View 2 RepliesI have a sheet with the following formula:
Code:
Column A Data:
007-006
007-006P
008-008
008-008P
Column F Data:
43,000
434,000
1,947,299
0
{=SUM(IF(RIGHT(TRIM(A4:A23),1)="P",0,F4:F23))}
When a row is inserted above row 24, the formula does not automatically include this row.
When this cell formula is modified manually, I get an error, unless I save the formula by pressing CTRL+SHIFT+ENTER.
How can I make it so that when a user inserts a line above this formula, to add more data, I can make sure that the formula automatically includes this row so the user will not get an error when trying to manually include this row. Users who will use this will know know this is a Array Formula.
Going off this excellent thread : [URL] ....
I'm looking to replace the words in
VB : myWords = Array("dog", "cat", "hamster")
with a named range.
Here is what I did. I added the lines:
VB:
Dim myArray As Variant
myArray = Range("colorbold")
And replaced mywords with
VB:
myWords = Array("colorbold")
Something is clearly wrong.
I have to excel files
1. Temperature & Humidity Reading
2. Summary
I tried to copy a date from Temperature & Humidity Reading file using the formula ='[Temperature & Humidity Reading.xlsx]Sensor 7'!$C$2 to Summary file
if the date format is like this 6/10/2013 12:00:00 AM the result is fine, but when i try to copy that formula for the succeeding dates the results is same from what i copied.
I have a byte array that contains a 4 byte floating point number. How would I convert this byte array to the single typed floating point number it contains?
If I have an assortment of odd and even values in range a1:j100, using the Array formula {=If(Mod(a1:j100,2),"",a1:j100)} will clear out all the odds, leaving the evens.
Is there a way I can add to this formula to color the qualifying cells (odds in this instance)? I know Conditional formatting is perfectly suited for the job but I am looking for a departure from this.
{=AVERAGE(IF(('Sales Reports'!$A$1:$A$5000=$A3)*('Sales Reports'!$C$1:$C$5000=$C$1),'Break Reports'!$D$1:$F$5000))}
and am copying it to other cells using this
Sub copyformula()
With Sheets("Sheet2")
.Range("B3:B5000").Formula = .Range("B3").Formula
End With
End Sub
It does the copy but removes the { and } thus rendering the formula useless to me. How can I accomplish this task and keep the formula as an array formula?
I tried to drag the formula down and recorded it as a macro but when it runs it takes far to long.
Essentially I have inherited a spreadsheet which is crammed full of complex array formulas.
No problem in itself as I understand array formulas pretty well (or so I think!). My problem is that when i want to ammend some of these, i cannot easily see what range I need to highlight in order to edit them i.e. I obviously cant do a single cell as its part of an array. I hope I have made myself clear?
So my question is this.
When the cursor is sat in a cell that is part of an array formula, how can I easily find out ( ideally by the array formula range being highlighted in (say) red ) what cells are included in the array formula?
Would conditional formating do the job?
I'm trying to graph my temperature readings.I put my readings in 1 page in order for me not to use many pages when Im printing.
Is there a way that if I graph my temperatures it will give me a continuous graph?
I'd like to try and use an array formula to return the minimum temperature between two dates. Say Column A holds a list of dates. Column B holds the temperatures for each of those dates in column A. Lets say that I want to return the minimum temperature between 10th Jan 2007 and 12th Jan 2007 with the daily temperatures being 9,10 and 11 respectively. I used the following array formula to return the maximum temperature between two dates and it succesfully returned 11: {=MAX( (A1:A10>=DATEVALUE("10/01/2007")) * (A1:A10<=DATEVALUE("12/01/2007")) * (B1:B10) )}
However when I try and use the MIN forumla the answer I get was 0 when it should have returned 9: {=MIN( (A1:A10>=DATEVALUE("10/01/2007")) * (A1:A10<=DATEVALUE("12/01/2007")) * (B1:B10) )}
I have an array formula that takes in a bunch of dates and returns the next date that is not in the past:
{=MIN(IF($N$4:$N$28<TODAY(),999999,$N$4:$N$28))}
This works great, but I'm wondering if there is a way I can write it to work on cells that are not adjacent to each other. For example, can I have it operate on only cells N4, N7, N15, and N22? Is there an easy way to do this?
I have spreadsheet that displays many blank cells. I would like all the information from this sheet (example below) displaying on another spreadsheet but without the blank cells.
On the example below I've shown how I want my sheet3 to look. (Cells B19:I22)
The sheet where I want to remove the blanks is Sheet2 B2:AE367
Is this possible using array formulas or macro?
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB1=BCDEFGHI1RedYellowBlueOrangeBlackGreenWhitePurple205/01/2008 27/01/20083 21/04/2008 4 31/03/2008 5 15/03/2008 617/01/2008 03/07/2008 7 22/08/2008 8 9 10/05/2008 25/09/2008 10 09/06/2008 28/02/200811 12 11/09/2008 1307/05/2008 12/11/2008 14 01/07/2008 15 16/10/2008 16 14/10/2008 17 18 19RedYellowBlueOrangeBlackGreenWhitePurple2005/01/200821/04/200831/03/200815/03/200803/07/200814/10/200825/09/200827/01/20082117/01/200810/05/200809/06/200822/08/200816/10/2008 12/11/200828/02/20082207/05/2008 01/07/200811/09/2008 Sheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
If I have the following data set in cells A1:C9:
IDCommentFirst Unique
109876Low OilTRUE
109876Checked On 12/12FALSE
109877Checked on 12/15TRUE
109878Correct LevelTRUE
109877Correct LevelFALSE
109878PerfectFALSE
109877NewFALSE
109878Correct LevelFALSE
In the First Unique column (column C) I have formulas. In C2 I have this formula (and then I copied it down):
=COUNTIF(A$2:A2,A2)=1
In cell E2, I have this formula to count unique occurrences:
=SUMPRODUCT(C2:C9*1)
In cell G2 I have this formula to extract unique records (entered with Ctrl + Shift + Enter):
=IF(ROWS(G$2:G2)
I have a long list (>1000) of increasing temperatures in column A. I use an array formula, {=MAX(A3:A1029-A2:A1028)} to find the largest change between two successive values. What I need to do then is obtain the two values that give rise to that difference (for instance, they might be in A678 and A679). I can't figure out a formula for this.
I can manage it if I insert a "difference" column in B, and use:
=INDEX($A$2:$B$1029,MATCH(MAX($B$2:$B$1029),$B$2:$B$1029,0)-1,1)
=INDEX($A$2:$B$1029,MATCH(MAX($B$2:$B$1029),$B$2:$B$1029,0),1)
but I'd rather keep the number of columns to a minimum.
I have a pivot table as shown below which is pretty straight forward however I am trying to create a macro that will automatically change the point name to match the point name in column E then copy that resulting dispaly to another sheet then pick the next point name in line and do the same thing and repeat for 50 rows, so my end result will be 50 pivot tables ready for printing. I can do this manually but I am trying to make it automatic...BTW the point names in column E change everyday but the pivot table supports the name changes.
Option Explicit
Sub PointName()
Dim Ws As Worksheet
Dim Rng As Range, Cel As Range
Set Ws = ActiveSheet
Set Rng = Range(Cells(2, 7), Cells(Rows.Count, 7).End(xlUp))
For Each Cel In Rng
Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
Ws.Columns("A:B").Copy
Sheets.Add
With ActiveSheet
.Paste
.Name = Trim(Cel)
.Range("A1").Select
End With
Next
Ws.Activate
End Sub
I turned it off for a while and when I turned it back on I am getting an error
Unable to set the _Default property of the PivotItem class
Debugger is highlighting
Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
I'm trying to find a single conditional formula which summarizes multiple text occurrences in a range of cells. I managed to summarize the occurrences in a single cell, but my challenge is that I'm looking for a single (array?) formula for this.
I have a worksheet with column A which contains the condition and column B which contains a text field. I'm looking for a single formula to summarize all the 'AB' occurrences in the cells B4..B11 where the column A is Apple. I managed to do this by using an additional column (column C), however as my worksheet contains over 10000 rows, I would like to use a single conditional (array?) formula summarize the AB occurrences (multiple ' AB's can occur in a single cell) in stead of having to use an additional field per row.
I have attached a sheet as an example. I'm looking for a single array formula in cell C11 which summarizes the 'AB' occurrences in cells B4..B11 where column A is Apple.
I got the following loop that is very slow. The main way (I think) to speed it up is to avoid looking at all the 65000 cells. The columns used in the calculations have data varying from a few hundred cells up to (rarely) dozens of thousands. I assume it would be better if I could replace the 65500 with something indicating the "end" of the data in the column. Also, is there another way to do this process without using a loop?
I am trying to fill in a number of rows with array formula's in the adjucent cells. I use the loop because the number of rows depends on a selection of another parameter which sets the row number before calling the loop.
Lets say I have a column, and i have some dates in that column. between each date entry are blank cells, an undetermined ammount.
ie:
------------
column1
------------
(blank)
1/1/2007
(blank)
(blank)
(blank)
2/5/2007
(blank)
(blank)
3/7/2007
(blank)
... etc ...
I would like some type of array formula, that could
agegrate that whole column into a new column, removing the blank cells.
so the new column would be:
--------
Column2
--------
1/1/2007
2/5/2007
3/7/2007
I was thinking something like... but this doesnt get rid of the blanks...
(this is using google spreadsheet, but ARRAYFORMULA, is the same as hitting "CTRL-ENTER" - in excel.
=ARRAYFORMULA(if(isnumber(A9:A17),A9:A17,0))
I'd like to point a cell to a subtotal on another sheet but that sheet will periodically have the subtotals removed, more data added, then sorted and subtotalled again. Is there a way to have my other cell find the subtotal that matches a certain criterion (in this case, cost center)?
View 9 Replies View RelatedI have attached the table with the values and have a red font for problem area.
I need to increase the cylinder filling temperature from 40deg.Celsius to 50deg.Celsius if possible.
O2 Cylinder filling.xls
I have a table with average temperatures(120,125,130...220) on the vertical axis and equipment sizes across the horizontal access (size 1,2,3...11). At the intersections is the amount of heat given off the equipment. This table is used as a reference for another sheet in the same workbook.
Basically what the sheet is for is to look up the average temperature of a piece of equipment (given the size and exact heat given off). In order to do this, I need to interpolate using the 2 closest energy values and their associated temperatures. There's a better explanation in the file and it makes for sense when you're looking at it. Attached is the table.
I had 4 initial temperature values and after some time i got new temperature values. Now for calculating 2nd iteration i need to use new calculated values and for third iteration values from 2nd iteration is to be used and so on.
How to calculate temperature values directly for 10th iteration without solving each iteration?
This problem just appeared recently, as if out of the blue, and it seems to affect all of my old work sheets as well as new ones. I am using 2002 version. When I input any number in any cell the program automatically inserts a decimal point. Thus 1 becomes .1, 12 becomes 1.2, 123 becomes 12.3 etc. I have tried formatting the cells to "general" and to to "number" with no change. The thing that really gets me is that old saved worksheets exhibit this behavior as well. I can work around the problem by inputting 1. or 12. which then gives me the entry I am after but I've never needed to do this before.
View 2 Replies View RelatedIs it possible to Conditionally Format using the (Custom) Data Validation Tool one or both of the following conditions:-
1. Force the user to enter the correct decimal point position for a given currency :-
The are 3 different decimal point locations location's - 0.87624 / 123.123 / 1273.00 There are always 6 numerals We have a table to lookup which specifies where the decimal point is on all currencies
CURRENCY AUDUSD< List is linked to a table
Price - High 0.87744< Decimal point dependant on chosen currency pair chosen
Price - Low 0.86596
Price - Close 0.86823
2. As above but numbers once entered will be formatted accordingly to the correct decimal place to the corresponding currency
I have instances where my Y variables sometimes contain a zero in the data and i need a formula to skip over that data point in the LINEST calculation.
Y variables are in Row 1, Columns A:E
X variables are in Row 2, Columns A:E
The following formula is returning a #VALUE! error: .....
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
I have a section of my program that asks for board scores (point value is 6-10 in increments of .5) I have set up a data validation so that users can only enter those specific values. Problem is, none of the values can be more than 1 point from each other. (the part I have yet to figure out)
Example, if the first value is a 6.5 the second is 7.0 and the third is a 9.0, I need the data validation to display an error because the 9.0 is beyond the one point spread.
Summary: Display an error message if any of the points are more than 1.0 point from another.
Is there anyway to recreate this formula w/o it being an array ?
{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}
I just wanted to concatenate a few cells using an array formula like this:
{=SUM(IF(B2:J2="";0;B2:J2))}
Well, this does not work. I have no idea why it fails. Any solution to this without scripting?
i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it
in writing so that i accurately describe my problem. attached is my example