Using an if statement. If a referenced cell is blank (or zero), I want to return a blank, non-zero cell. Possible (no vb)?

I need it to be non-zero because I'm using the if function to pull a certain section of data, out of a set, and then plot that chosen data (and not having 0 values in the chart).

Ex: A1-A3 are blank/zero; A4-A10 have values

{=If(A1:A10=0,blank, non-zero,A1:A10)}

Using "", etc are picked up as zeros

12/4/2008 5:42:23 PM

try NA()

12/4/2008 5:50:52 PM

hmmm... that would work for this. but it would screw up any future formulas that referenced the NA cells wouldn't it?

12/4/2008 5:53:02 PM

Yeah, but I'm not sure there's an easy way around it.

You might end up creating two sets of data--one for formulas and one for plotting. Link them together using an if statement similar to what you have.

12/4/2008 6:05:52 PM

that's what I'm currently doing

shit. I'm a dumbass.

I have a sheet of data, the first column are dates, then another column has values in it, associated with that date (just numbers). If I want to pull data, per month, into separate sheets (tabs), how can I do this. My way of thinking didn't work. The data's not evenly spaced, in time (some months have more data than others).

EX:

A1:A10 are a few days in January (date format)
B1:B10 have a number in them
A11:A20 are a few days in February (date format)
B11:B20 have a number in them

and so on (but thousands of cells).

How can I pull the data from rows 1 - 10 into a sheet, and data in rows 11-20 into another sheet.

I want to do this so I can just copy the sheet and it automatically pulls the next month's informaton into a new sheet (with a few adjustments, of course).

Again, zero/non-zero cells in the master sheet must be non-zero cells in the individual sheets (NA() will suffice)

[Edited on December 4, 2008 at 6:20 PM. Reason : .]

[Edited on December 4, 2008 at 6:20 PM. Reason : .]

12/4/2008 6:06:59 PM

what would you use #N/A values for in other formulas?
If you need to reference those cells, you can add another IF statement inside the formula checking for #N/A, and if true, ignore the cell.
e.g.

IF(ISERROR(A1),"",do something else)

12/4/2008 6:27:32 PM

you shouldn't have any problem using " ". (note, that's quote-space-quote). otherwise check the formatting of your cells.

12/4/2008 6:28:30 PM

^I meant if I were to use a formula to sum a column, it wouldn't work with NA cells. is there a way to do formulas like this, with NA cells in the range? I understand that I can use the if-statement you used, some of the time, but not with all functions.


^^ using "" or " " resulted in the chart reading those cells as zeros, not blank cells.

[Edited on December 4, 2008 at 7:07 PM. Reason : .]

12/4/2008 7:05:37 PM

[Edited on December 4, 2008 at 8:39 PM. Reason : .]

12/4/2008 8:39:27 PM

I'm thinking I need vb to be able to do everything I need to do.

Guess I'll start googling. Anyone know of good books/have one?

12/4/2008 9:09:21 PM

Some combination of vlookup and index.

12/4/2008 9:19:45 PM

Could do this in VB in about 20 lines.

12/4/2008 9:38:38 PM

Dim theRows As Integer
Dim destMarker(12) As Integer

theRows = 1
While Cells(theRows, 1) > 0
   
   destMarker(Month(Cells(theRows, 1))) = destMarker(Month(Cells(theRows, 1))) + 1
   Sheets(Month(Cells(theRows, 1)) + 1).Cells(destMarker(Month(Cells(theRows, 1))), 1) = Cells(theRows, 1)
   Sheets(Month(Cells(theRows, 1)) + 1).Cells(destMarker(Month(Cells(theRows, 1))), 2) = Cells(theRows, 2)
   theRows = theRows + 1
Wend

12/4/2008 10:13:33 PM

^thanks. I'll give it a try next week.

also, does anyone have any books/hard copies of how to learn to write vb? I can't really read a computer screen for long periods/long articles.

[Edited on December 5, 2008 at 7:15 PM. Reason : .]

12/5/2008 7:08:35 PM