w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Excel formula -> how to change SUMPRODUCT formula to skip null cells
In the sumproduct, to exclude empty cells, suppose you're using it on range A1:A100, you could do the following: = Sumproduct((A1:A100),--(A1:A100<>"")) That second criteria will ensure that you're only looking at cells that have a value in them... As an explanation (A1:A100<>"") will return an array of True False, where, if there is a value in the cell, it returns true, otherwise, false. Then, including the -- before it, it converts True/False to 1/0. So, in effect, you're multiplying empty cells by a zero (excluding from the formula) and non-empty cells by 1 (including them in the formula). The --(logical statement for my array) is a very useful trick to use with SumProduct() in MANY different ways!!

Categories : Excel

In Excel, how to fill blank cells with the last preceding non-blank cell?
In a "manual" fashion, you could do the following: Select the column, go to Edit > Go To > Special > Blanks, type = in the formula bars, hit the up arrow then Ctrl+Enter. you should now have the cells fill with the values above In a more "automated" fashion using VBA, you could do the following: Select the range where you want to fill the blank cells, Then click on Developer Tab -> Visual Basic -> Insert -> Module Then paste the code in the Module, and click on run: Code: Sub Fill_Blank_Cells() Selection.SpecialCells(xlCellTypeBlanks).Select Selection.FormulaR1C1 = "=R[-1]C" End Sub As @Jerry rightfully pointed out, make sure to copy-paste special the values in order to remove the formulas behind your filled column. This will prevent any undesirable results later!

Categories : Excel

Referring to a range of cells inside a formula in VBA Excel
If it is possible, turn your data into a table. This way, you can refer to dynamic data (changing in size) without running into problems. For instance, to refer to column A, called 'Item' in a table called 'Table1', your reference would be '=Table1[Item]' Regardless of the rows.

Categories : Excel

Formula in excel not considering dynamically generated values in cells
In case your sheet has some formula for say A3=sum(A1:A2) is calculated and it is working fine with A1=5, A2 =4. Now if change the value of A2 with 2 like sh.getRow(0).getCell(0).setCellValue(2); // set A1=2 and perform write operation, check the value of A3 it is still showing 9. It seems wrong but actully not. The point is that Excel caches previously calculated results and you need to trigger recalculation to updated them. wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); or with wb.setForceFormulaRecalculation(true); and then perform Write operation. This will give you the correct result. For Detail check here

Categories : Excel

Excel formula to the value of a cell based on two cells' values
In case this helps, the following formulae seem to display the values at the corners, assuming Measure is a named range starting in A1 and value1 and value2 are named ranges: =INDEX(Measure,IFERROR(MATCH(value1,A:A,0),MATCH(value1,A:A,1)),IFERROR(MATCH(value2,1:1,0),MATCH(value2,1:1,1))) =INDEX(Measure,IFERROR(MATCH(value1,A:A,0),MATCH(value1,A:A,1)),IFERROR(MATCH(value2,1:1,0),MATCH(value2,1:1,1)+1)) =INDEX(Measure,IFERROR(MATCH(value1,A:A,0),MATCH(value1,A:A,1)+1),IFERROR(MATCH(value2,1:1,0),MATCH(value2,1:1,1))) =INDEX(Measure,IFERROR(MATCH(value1,A:A,0),MATCH(value1,A:A,1)+1),IFERROR(MATCH(value2,1:1,0),MATCH(value2,1:1,1)+1))

Categories : Excel

Excel: get cell collection of cells that contain certain text or formula
Paste this macro in your macro editor in your workbook (ALT+F11) Sub test() Dim w As Worksheet, r As Range For Each w In ThisWorkbook.Worksheets For Each r In w.UsedRange If r.Value = "Certain text" Or r.Formula = "Certain Formula" Then 'Do what you want to do here End If Next Next End Sub Hope this helps

Categories : C#

Select and Copy non blank cells in excel
Dim fso As FileSystemObject Dim stream As TextStream Dim str As String Set fso = New FileSystemObject Set stream = fso.CreateTextFile("c:myTextFile.txt", True) For i = 1 To 10 For Each cell In Range("A" & i & "F" & i) If Not IsEmpty(cell) Then str = str + cell.Text + " " End If Next cell stream.writeline (str) str = "" Next i stream.Close End Sub all you gotta do is change what you wanted separated by ( the " " at the end of the str line) and the range you want ( i = rows 1 through 10 as is) and ("a" & i ":f" & i which indicates a through f, in this case for rows 1 through 10) hope this helps

Categories : Excel

Graphing blank cells in form of gaps in Excel
Instead of putting zeros or empty strings try to put #N/A. You can do it with a formula like =IF([test],[value],NA()). This will allow the graph not to show the missing values as zeros, but if I understand your question, it is still not what you want, because you want the missing values to be represented as gaps, not as missing values. The only way that I know of to see the gaps is to use a scattered graph. As far as I know, all the graphs that make a line to join two points, do join two points, and don't have the concept of missing point. They just join the two closest points. A solution could be to make a VBA macro that goes inside the graph and changes the color of each graph line when the data is missing.

Categories : Excel

Is there a way to exclude blank excel cells on import using oledbadapter in a datagridview?
One problem is you need to work from the bottom up on the DGV: For i As Integer = DataGridView1.Rows.Count - 1 To 0 If you work from the top down the index of any given row below the row removed changes.

Categories : Vb.Net

How to create shared formula in Excel via Automation
We can use Range.copy for creating a shared formula. And the parameter of this method is the target range. Please look at the below sample: Excel.Worksheet objWorkSheet= (Excel.Worksheet)Globals.ThisWorkbook.ActiveSheet; Excel.Range oResizeRange; objWorkSheet.Cells[1, 5] = "1.33"; objWorkSheet.Cells[2, 5] = "1.565"; objWorkSheet.Cells[3, 5] = "1"; objWorkSheet.Cells[4, 5] = "1"; objWorkSheet.Cells[5, 5] = "1"; objWorkSheet.Cells[6, 5] = "1"; oResizeRange = objWorkSheet.get_Range("E8", "E8").get_Resize(Missing.Value, 1); oResizeRange.Formula = "=SUM(E2:E6)"; objWorkSheet.Range["F1"].Formula = "=E1*10"; objWorkSheet.Range["F1"].Copy(objWorkSheet.get_Range(oWS.Range["F2"], objWorkSheet.Range["F8"]));

Categories : Excel

Create Excel Chart within Excel File
Till now as the apache POI limitation is saying "You can not currently create charts. You can however create a chart in Excel, modify the chart data values using HSSF and write a new spreadsheet out. This is possible because POI attempts to keep existing records intact as far as possible". However in my case, I have created a chart manually on excel sheet using Named Ranges, and using java, I am updating named ranges as per my requirement. Since the chart is based on named ranges so it also get updated. For updation please check here

Categories : Java

Groovy POI created cells considered blank by other programs until ENTER is manually pressed in cells
I have no experience with Java, but having to hit enter in a cell is usually indicative of calculation being set to manual. Since you are working with API it may be treating your input from Java as a formula (?). Open the offending workbook in its broken state, and on the sheet you are having problems with choose [Formulas] tab at top, then [Calculation]>Calculate Sheet. If this updates the value then I see two options... See if there is an option to manually calculate the sheet with POI, after the value has been entered. Alternatively, you can write just a tiny bit of VBA to force calculation on that sheet when workbook is opened. MSDN offers a simple example. http://msdn.microsoft.com/en-us/library/office/aa223802%28v=office.11%29.aspx I have had to do this for Excel apps w

Categories : Excel

How does Aspose.Cells read/create MS Excel file
Aspose.Cells for .NET reads and writes the Excel file directly. It does not have any dependency on any other COM component. The only requirement is the .NET Framework. I work as a Developer Evangelist for Aspose.

Categories : C#

Create Temporary Excel Chart
You could use the SelectionChange event of the worksheet to delete the chart. Below I am assuming there is only one shape - the chart - that might be on the worksheet. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Me.Shapes.Count = 1 Then Me.Shapes(1).Delete End If End Sub It is possible (I believe) to dynamically attach this event, and remove it. However, I believe that it is a little complicated. An alternative might be to use Application.OnTime to delete it after a period of time. Application.OnTime Now + TimeValue("00:00:40"), "ProcedureToDelete" will run the procedure named 'ProcedureToDelete' after 40 seconds. In this procedure you might want to make sure the the selection is in the worksheet, not in the chart that you are about to delete. Yo

Categories : Excel

Selecting blank and non-blank cells using google script
If I'm understanding correctly, this might me one way: var counter = 1; for (var i = 1, length = data.length; i <= length; i++) { if (i == length || data[i][0]) { sheet.getRange(i + row - counter, 1, counter, 3).setBorder(true,true,true,true,true,false); counter = 1; } else { counter++; } }

Categories : Javascript

For an Excel Formula's variable taking a Range, input row number by the result of a formula
Try using the INDIRECT function:) say you have three cell that can dynamically calculate the address of your input range. something like what you suggested and it is in the cell A1 ="$BB$2:$BB$" & COUNTA(BB2:BB1000) ---> in cell A1 You can use the Indirect function like this: =SERIES(INDIRECT(A1),[RANGE],[RANGE]) Hope this helps!

Categories : Excel

How to Create multiple word documents(blank) thru excel macro, with specific name?
I've quickly whipped this up, which opens your word document, saves it based on the directory in Range("A2"), with the filename in Range("B2"), and then closes the file. Sub Button1_Click() Dim oWord As Word.Application Set oWord = New Word.Application oWord.Visible = True oWord.Documents.Add Template:="Normal", NewTemplate:=False, DocumentType:=0 oWord.ActiveDocument.SaveAs2 Filename:=Range("A2") & Range("B2") oWord.ActiveDocument.Close SaveChanges:=False oWord.Quit Set oWord = Nothing End Sub This should provide you with a good basis for cycling through your A column, and save the documents, and then correcting any empty directories (can be done with an if statement). You will have to ensure that you have the Microsoft Word Reference checked - to do this, ensure you're in the

Categories : Excel

Create an Excel chart with VBA without manually inputing a range
Try to change the following line: ActiveChart.SetSourceData Source:=Range("'OBA 1'!$A$1" & "r.Columns.Coun, r.Rows.Coun") into (assuming that Activesheet is OBA 1 sheet: ActiveChart.SetSourceData Source:=r or into (in other situations): ActiveChart.SetSourceData Source:=Range("'OBA 1'!" & r.Address)

Categories : Excel

Convert Java/C style formula to excel formula
You can use built in Excel functions to substitute desired strings instead of other determined strings, such as: =SUBSTITUTE(D2,"AND"," == ") You can also read the formula into a string in VBA, perform the same operation, and place the formula back in excel. The strFormula = Cells(2, 4).Formula strFormula = Replace(strFormula, "AND", " == ") Cells(2, 4).Formula = strFormula I hope this helps.

Categories : Misc

Select multiple columns in current region to create bar chart using Excel VBA
Instead of this: Source.Worksheets("Sheet1").Range(Search.Address).Offset(2, 0). _ CurrentRegion.Activate Use this, which contracts the selected area so as to exclude the last row and last column of the data table: Dim tbl As Range Set tbl = Source.Worksheets("Sheet1").Range(Search.Address).Offset(2, 0). _ CurrentRegion tbl.Resize(tbl.Rows.Count - 1, tbl.Columns.Count - 1).Select While continuing to comment out or delete: 'ActiveChart.SetSourceData Source:=ActiveWorkbook.Sheets(1). _ Range(Search.Address).Offset(2,0).CurrentRegion. _ Resize(CR.Rows.Count - 1, CR.Columns.Count)

Categories : Excel

Trying to calculate a formula that gets closer to e but program just wont work
Change y=(2*x+1)/(2*x-1) to y = 1.*(2*x+1)/(2*x-1). Otherwise, y is being rounded to one, and you're stuck in the infinite loop. To see this, simply print y at each iteration: printf("%d %.7f %.7f ",x,z, y); ^^^^ ^

Categories : Function

SQL Formula Answer is Different from Excel Formula
Firstly, the question is tagged with MySQL, but the syntax used looks suspiciously like SQL-Server, as such I will do all the demonstrations with SQL-Server syntax. Secondly, based on the magnitude of the error (to the order of 10), I think you have your calculations mixed up, you appear to be saying that in the below 3 columns sum ([Impacts]) as 'column1', sum (nullif([Resp 2],0)) as 'column2', sum (nullif([Resp 2],0)/nullif([Impacts]*1000,0)) as 'column3' the third column is not giving you the same result as if you did the (column2 / (column1 * 1000) in excel, however this is not the same formula. I think what you need is: NULLIF(SUM([Resp 2]), 0) / (NULLIF(SUM(Impacts) * 1000, 0) AS [Resp 2%] Imagine this simple example (I have removed the NULLIF operators to make the difference

Categories : SQL

Excel Compare two cells in one sheet to two in another, copy a certain range of cells if they are equal
edited July 23 - 10:30EST no VBA or macro needed you can use simple formulas and some copy and paste on sheet2, at the end of the data columns, create a new column (AD?) with this formula =IF(AND(IFERROR(VLOOKUP(A4,[book1]sheet1!$C$4:$C$14,1,FALSE),"")<>"",IFERROR(VLOOKUP(D4,[book1]sheet1!$L$4:$L$14,1,FALSE),"")<>""),[book1]sheet1!T4,X4) and copy it over the next 6 rows (AD to AI) I assumed the first row being 4 and the formula is searching 10 rows in sheet 1, adjust as needed then copy down to last of your data rows if you have a match, the sheet1 data will be displayed otherwise, sheet2 data will be there copy this chunk, go to X4 and use EDIT - PASTE SPECIAL - VALUES if excel 2003 or click the PASTE button on upper left and choose PASTE VALUES if Excel 2007+ I rec

Categories : Excel

Generating a new Excel while retaining old formula from template Excel file
If you have access to the Cell, simply call: String formula = cell.getCellFormula(); Note though that this will only work if the cell is formula cell. IE: cell.getCellType() == Cell.CELL_TYPE_FORMULA. If the cell is not a formula cell, you're going to end up with an IllegalStateException as described in the API

Categories : Java

Why is it only showing a blank? Why wont it parse correctly?
static void compile(string line, string code) You're passing code in as a string, but not requesting it to return with any data, yet your usage of the function clearly states that you're trying to alter it with each call. Change the call to a ref. static void compile(string line, ref string code) and call it with a ref, e.g. compile("function Main {", ref code);

Categories : C#

How to compare 2 cells to another 2 cells in a different sheet in excel?
If you put =A1&B1 in C1 of each sheet and copy down then it is like comparing single columns. So with, in D1: in Sheet1: =MATCH(C1,Sheet2!C:C,0) in Sheet2: =MATCH(C1,Sheet1!C:C,0) copied down in each case the resulting numbers should show the row number where the match is on the 'other sheet' (the lists need not be sorted) and otherwise #N/A for where matches have not been found. There is obviously no need for the concatenation for the example provided, since the two 'A' columns are identical, but I take it that was because the example was simplified.

Categories : Excel

Formatting cells through a custom formula
A function can only return a value, but the event macro: Private Sub Worksheet_Change(ByVal Target As Range) Target.Interior.Color = Target.Value End Sub will get you the desired functionality.

Categories : Excel

Empty Cells which has vlookup formula
manimatters has a good solution to use a ISBLANK with an IF formula. If you still want a macro for your second question, I've provided it below. This macro will set the formula in C2 to =VLOOKUP($B2,Sheet1!$A$2:$C$101,2,FALSE) and then fill this formula down to C100. Sub RestoreVLookup() Range("C2").FormulaR1C1 = "=VLOOKUP(R[0]C2,Sheet1!R1C1:R101C3,2,FALSE)" Range("C2").Select Selection.AutoFill Destination:=Range("C2:C100"), Type:=xlFillDefault End Sub Let's look at the different parts: Range("C2").FormulaR1C1 - Assign a formula to cell C2. "=VLOOKUP(R[0]C2,Sheet1!R1C1:R101C3,2,FALSE)" This is the formula assigned R[0] - Relative row with an offset of zero. R[1] would point to the row above and R[-1] would refer to the row below. C2 - The 2nd column (aka colum

Categories : Excel

Excel VBA Formula not working in German excel
This wouldn't work under English locale, too. * & is not a valid sequence of operators. My psychic powers tell me you want to embed Calc into the formula as a literal, in which case it should be Range("A2").Formula = "= B2 *C2 * " & Str$(Calc) You have to use Str to have Calc converted into a string according to the English locale, required by Formula. You could leave it as is if using FormulaLocal.

Categories : Excel

Excel 2003 XYZ Surface Chart like XY Line Chart
Since I'm not using Excel 2003 I fear this may be no help at all (unless perhaps to clarify your requirement) but if the chart shown is what you seek then it may be helpful to know that this was with the addition of series for 4 with Series values: as shown (rather than a lot of zeros in source data): Edit I prefer time on the x-axis but if required on the z-axis:

Categories : Excel

Formula Omits Adjacent Cells error EPPlus
As far as I know, EPPlus doesn't have a calculation engine. This means cell A5 doesn't actually contain the calculated formula result value for "=SUM(A3:A4)". And judging from your code, I suspect A5 didn't exist with a value, only a formula part. (Yes there are 2 parts for each cell: a formula part and a value part). So something like this: ws.Cells["A5"].Value.ToString(); will fail with your "Object reference not set to an instance of an object." error.

Categories : C#

How to read Excel with merge cells and save the Excel to a dict in python
Not sure about excel documents, but you can read files in CSV format with the csv module. From the docs: >>> import csv >>> with open('eggs.csv', 'rb') as csvfile: ... spamreader = csv.reader(csvfile, delimiter=' ', quotechar='|') ... for row in spamreader: ... print ', '.join(row) Spam, Spam, Spam, Spam, Spam, Baked Beans Spam, Lovely Spam, Wonderful Spam Testing with your file as outputted from LibreOffice (I don't have Excel on this machine), merged cells are split and padded with blank cells (as though they weren't merged in the first place). So you'd have something like: [['S1', 'S2', '', '', 'S3', '', ''], ['a', 'a1', 'a2', 'a3', 'a4', 'a5', 'a6'], ['b', 'b1', 'b2', 'b3', 'b4', 'b5', 'b6'], ... etc] Then you just need a script to convert thi

Categories : Python

VBA-charts: how to read the formula from a chart Series
FullSeriesCollection is for Excel 2013 which I do not have currently. However, the following code displays the series formula in Excel 2010 and it should, I believe, work for 2013: Sub Blah1() Dim cht As ChartObject Set cht = ActiveSheet.ChartObjects(1) MsgBox cht.Chart.SeriesCollection(1).Formula End Sub If the chart is not an embedded chart then the following equivalent code (for a Chart sheet) also works: Sub Blah2() Dim cht As Chart Set cht = ActiveChart MsgBox cht.SeriesCollection(1).Formula End Sub

Categories : Excel

remove blank cells from datatable
Ideally you only want to remove a row when ALL of its cells are empty. The algo you have shown will delete the row even if a single cell in it is empty. You should change your code like this: First remove empty rows: For i = 0 To dt.Rows.Count - 1 flag = True For j = 0 To dt.Columns.Count - 1 If Not(dt.Rows(i).IsNull(j)) AndAlso dt.Rows(i)(j).ToString() <> "" Then flag = False Exit For End If Next If flag Then dt.Rows(i).Delete() Next dt.AcceptChanges() Now remove empty columns: For i = 0 To dt.Columns.Count - 1 flag = True For j = 0 To dt.Rows.Count - 1 If Not(dt.Rows(j).IsNull(i)) AndAlso dt.Rows(j)(i).ToString() <> "" Then flag = False Exit For End If Next

Categories : C#

Macro to check for blank cells and highlight them
Try the following: Sub testborder() Dim rRng As Range, row As Range, c As Range Set rRng = Sheet1.Range("B2:D5") 'Clear existing rRng.Borders.LineStyle = xlNone For Each row In rRng.Rows For Each c In row.Columns 'Apply new borders If (c.Value > "") Then c.BorderAround xlContinuous Next c Next row End Sub Or, with an even simpler loop: For Each c In rRng.Cells 'Apply new borders If (c.Value > "") Then c.BorderAround xlContinuous Next c

Categories : Excel

Deleting blank spaces from Googledoc Cells
From http://www.javascripter.net/faq/trim.htm, this a great function for trimming white spaces in javascript. I've used this in google docs js as well. str = str.replace(/^s+|s+$/g,'');

Categories : Javascript

Macro Find Blank cells and Replace
I'd use SpecialCells: Sub fillblanks() Dim rngBlanks As Range Dim rng As Range Dim cl As Range Set rng = ActiveSheet.UsedRange.Columns(2) Set rngBlanks = rng.SpecialCells(xlCellTypeBlanks) For Each cl In rngBlanks.Cells With cl If (.Value = "") And (.Offset(0, -1).Value <> "") Then .Value = .Offset(0, -1).Value End If End With Next End Sub

Categories : Excel

VBA Blank Cells offsetting data when copying
"I think i have some of the syntax wrong" is not helpful. You need to tell us the error message you receive and the line that this error refers to. But this is probably wrong: WS.Range(.Cells(10, 7)) unless .Cells(10, 7) happens to contain a cell address. If you want further assistance you need to ask a specific question. Added If Cells(10, 7) = "" Then I assume this should be looking in the "Macro Test Sheet" so this should also be preceded by a dot, otherwise it is looking in whatever is currently the ActiveSheet.

Categories : Vba

android: webview show blank screen when i show pdf file from dropbox link
WebView Can't open pdf You need to use google doc for View Pdf or you can use AdobeReadr Pdf protected void onCreate(Bundle savedInstanceState) { // TODO Auto-generated method stub super.onCreate(savedInstanceState); setContentView(R.layout.activity_wbview); mWebView=(WebView) findViewById(R.id.web_view); String weblink="https://dl.dropboxusercontent.com/u/107860028/Publishing%20an%20Application%20on%20Google%20Play.pdf"; mWebView.getSettings().setJavaScriptEnabled(true); webview.loadUrl("http://docs.google.com/gview?embedded=true&url=+weblink"); }

Categories : Android

uitableview cells blank but correct text in console
In the table view there is this concept of reusing the cells. And the if block cell==nil is only run when there is no cell to be reused by the table view. So you still need to set the title for the cell no matter whether you are using a newly allocated cell or reused cell. Take out the following line from the if block and see if it works: cell.textLabel.text=[NSString stringWithFormat:@"site number %@",[sites objectAtIndex:row]];

Categories : IOS



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.