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 nonempty 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!!

In Excel, how to fill blank cells with the last preceding nonblank 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 copypaste special the
values in order to remove the formulas behind your filled column. This will
prevent any undesirable results later!

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.

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

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))

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

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

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.

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.

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"]));

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

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/enus/library/office/aa223802%28v=office.11%29.aspx
I have had to do this for Excel apps w

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.

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

Selecting blank and nonblank 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++;
}
}

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!

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

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)

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.

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)

Trying to calculate a formula that gets closer to e but program just wont work 
Change y=(2*x+1)/(2*x1) to y = 1.*(2*x+1)/(2*x1).
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);
^^^^ ^

SQL Formula Answer is Different from Excel Formula 
Firstly, the question is tagged with MySQL, but the syntax used looks
suspiciously like SQLServer, as such I will do all the demonstrations with
SQLServer 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

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

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

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);

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.

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.

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

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.

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 xaxis but if required on the zaxis:

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.

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

VBAcharts: 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

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

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

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,'');

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

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.

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 Autogenerated 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");
}

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]];
