I have a series of calculations that I need to use the MIN formula. the

problem is that many of the cells can have a 0(zero) value and not just

blank. Is there a way to find the minimum value but ignore the 0's? Otherwise

the minimum is always 0.

Thanks

problem is that many of the cells can have a 0(zero) value and not just

blank. Is there a way to find the minimum value but ignore the 0's? Otherwise

the minimum is always 0.

Thanks

- How to average a set of values but ignore zero and error values (#N/A)?
- 0 (zero) values shown in line graph for empty values - (EDIT
- Formula to count the number of times a non-zero value appears three consecutive times
- Function to find first non-zero value in a range
- Show zero value instead of a minus number
- Need V-Lookup to return a 0 not # N/A when data not present
- 0 VS #n/a
- Using sumif to sum a range of values
- How to enter a null cell value in a formula
- Look for min value in a Column,but ignore #VALUE! / 0 (zero)
- Ignore ZERO values in PIE Chart
- From #N/A to "0" Value
- Ignoring a blank cell (not interpreting it as a zero)
- How to hide rows in a Workbook with multiple sheets with zero valu
- CALCULATING A FORMULA TO USE A SPECIFIC CELL'S VALUES
- How to omit zero values when calculating averages?
- How to add a default value (if less than function) to a calculation
- Is there a way to use a calculation as column identifier number??
- Delete Sheet If Cell Value Equals 0
- Need to add a 0 (zero) to an incorrectly formatted time
- How to hide zero values rows in a range of sheets
- Sum To Ignore Negative Values
- Min Function Excluding Zero Values & More

How to average a set of values but ignore zero (0) and error values (#N/A, #value!)? Been searching for a way to do this. Lots of forums on how to ignore zero value OR ignore error values but not ignore both. Appreciate the assistance.

Regards,

klchoy

I get the following situation in a graph:

------ Jan Feb Mar Apr May

Actual 5 6 8

Budget 6 5 5 0 0

Why does the line not show the same behaviour as the bar ? I do not want the line to show 0 values or "" as 0 (zero).

BTW. Options-Tools-Graph shows: "Plot empty cells as: Not Plotted (leave gaps)"'

Apparently this only works for the Bar-Graph.

What can I do or is this just a peculiar habit of Excel2000 ?

I am trying to create a formula that will count how many times a non-zero value appears three consecutive times. For example, the formula should return "4" in the following example:

Cells B1:P1 contain the following values: 111011111100111

The first string of "111" values count as 1 instance + the next string of six "111" values count as 2 instances because we have back to back "111"s + the last set of "111" values count as 1 instance = 4

Handling the string of six consecutive "1" values seems to be the biggest challenge.

Would appreciate any guidance! Thanks in advance.

I have sort of a complicated question (for me) and I was hoping that one of you could help me out.

Here's my example.

I have two columns of data (I'll call them A & B):

A | B

Phase 1 | Blank

Phase 2 | Blank

Phase 3 | Blank

Phase 4 | XXX

Phase 5 | Blank

Total | YYY

What I'm trying to do, is have the total (YYY) return the value 4 (as in 4 phases). I've tried using the counta() function, but it only works if I enter values for the other phases (which isn't always the case). And the match() function only works if there's an actual lookup value (which in this case it changes all the time). I'm looking for some sort of lookup function (or something like that) which will return the line number a non-zero value entered in column B. That non-zero value can be anything however.

Any help that you guys could provide would be greatly appreciated!

Thanks,

Paula

I need help with a formula that will give me a 0 (zero) value

cell A1+A2 sum in A3....no problem here

Then in A4 I need a sum of another cell total lets say B1 taken away from sum in A3

e.g A1=10

A2=10

sumA3=20 if cell B1 is more than 20 it will give a negative number

I need A4 to show 0 or the positive number

tried for hours any help please

[ This Message was edited by: tigger1 on 2002-11-08 15:34 ]

=vlookup($d$3,'sales by month'!$k$4:$q$90,2,'sales by month'!$k$4:$k$90)

In other words I would like to sum a range of values in col c if values in

col b are equal to "2006" and values f are equal to "proposed".

Thanks

--

hwy

B2, I might want to have B2 be NULL if A2 is NOT NULL, otherwise use the

value from B1). This might be written something like "=if(A2>"",NULL,B1)".

The intent is to allow the text value of A2 to extend into B2 (i.e. not

wrap). Entering a "" for the TRUE action does not allow A2 to extend into B2.

The first sheet (List) has a list of words in German (column B) and the English translation (column C). Column A assigns a number value to each word, which is referenced by the sheet “Flashcards”. Column D is the “Score” for each word.

On the second sheet (Flashcards) cell C7 takes a random number from List.A:A which cell E7 uses to pull that number’s word in English or German.

On that same sheet, I have two command buttons. I would like a macro to add 1 to that word’s score (first sheet, column D) if you click “Correct”. The second macro would deduct 1 upon clicking “Incorrect”

(Note: this is a growing list so it needs to be able reference the entire column)

I’m a noobie to both VBA and this site so any help is much appreciated!

I have a formula "=MIN(C:C)" to look for the min value in Column C. is there a way I can tell the formula ignore any cell value in Column C that has either #VALUE! OR 0 (zero) value.

Thanks in advance,

I am trying to build a PIE chart from the hereunder values:

1, 3, 0, 0, 7, 0, 11

I need a way to force the chrat to ignore all the Zeros.

In a normal BAR or LINE chart I would have used the =NA() or #N/A instead teh ZEROs but in a PIE chart it seems to have no influence on the ZERO Lables.

Bottom line:

I am looking for an automatic way to eliminate displaying only the ZERO Values (Lables) and to present a PIE chart made of 3 pieces and 3 Value Lables.

Thanks, Michael

=INDEX($A$6:$H$95,MATCH("FPL",$A$6:$A$95,),MATCH(4,$A$6:$H$6,))

Thank you,

Jena

I have generated a spreadsheet which allows a user to enter particular values along a row (total of 12 values in 12 cells). 5 days a week, only 6 of these cells are filled out, and 2 days a week, all 12 values are filled out. The cell values are automatically charted on a different page of the spreadsheet which allows me to track certain events over time. This is where I have a problem.

Everything is fine during the two days of the week that the entire 12 cells are filled out. However, when only 6 cells are filled out, it skews the data interpreting no entry as a 0 (zero). Is there anyway that I can enter in an equation or macro which would cause the charted data to ignore data that is not entered? Something like ignore data that is =0 or blank?

Thanks!

I am trying to create a code to run on a workbook with several work sheets

to hide rows with zero values.

I have used the following code:

Sub HideRowsIfColumnDisEmpty()

Dim X As Long

Dim LastRowOfData As Long

With Worksheets("Functional SummaryTotal Risk")

LastRowOfData = .Cells(.Rows.Count, "N").End(xlUp).Row

For X = 1 To LastRowOfData

If .Cells(X, "N").Value = 0 Then

.Cells(X, "N").EntireRow.Hidden = True

End If

Next

End With

End Sub

This works for one worksheet in a workbook, however I need this to work on

multiple sheets.

Could someone please help me on how I can modify this could so that it can

run successfully on multipule worksheets in a workbook.

Thank you.

Khurram

I am completely stumped at the moment. I am trying to use an IF formula or

a COUNTIF formula to capture a specific cells value.

What I need is to determine whether or not a certain value in a cell is

equal to or lower than 0 (zero). If so, I want to use the value in this

cell. Alternatively, if it is equal to or higher than 0 (zero) I want this

value to be used in another cell.

Can anyone help me to figure the formula out for this? It would be

extremely helpful as I know it can be done. Its a matter of determining the

correct way to write the forumla.

Cheers

Paul

I'd like to find a method that allows me to calculate an ongoing YTD average that omits any zero values present in the source data range.

Any zero values in the source data are not valid, so the purpose of omitting them serves to avoid my YTD average becoming skewed due to inclusion of the zero values.

Below is an example of the formula I'm using (pls see attached screenshot for further reference).

G23: =AVERAGE(L23)

G24: =AVERAGE($L$23:L24)

G25: Etc.

screenshot - ss.jpg

Thanks for any assistance that can be provided,

Jason

I can't seem to figure out how to do this in excel? FoxPro no problems, Excel and I have problems? Go figure.

Currently I'm dealing with this

=IF(C4="",0,IF(C4>=1,(B4+D4+E4+F4+H4)))

My problem is I want to add a minimum default value to this calculation.

If my return from this string is less than $25 I want to return the value of $25 otherwise if it is greater than $25 I want to return the calculation of this string?

Any help would be greatly appreciated. Thanks!

So... worksheet1 has main set of information in single row format, and worksheet2 relates back to worksheet 1, but is set in rows of 4 cell blocks for every 1 row in worksheet1

thus... worksheet2 B2:B5 have to return worksheet1 B1 B6:B9 have to return worksheet1 B2 and so on down the sheet. table example below.

The formula below will return the row number i need to identify for each cell block- but i cant seem to figure out way to make the formula replace the digit after the column identifier

(ROUNDUP((ROW()+3)/4,0)

this is what will not work :-)

=Worksheet1!A(ROUNDUP((ROW()+3)/4,0)

I would love to get this working - help would be so much appreciated!!

Example

Worksheet 1

A B C D E 1 Fruit

Color

2 apple red 3 orange orange 4 cherry red 5 pear green 6

Worksheet 2

A B C D E 1 ID

Fruit

COLOR

ROW # NEEDED

2 yes apple red 1 3 apple red 1 4 apple red 1 5 apple red 1 6 yes orange orange 2 7 orange orange 2 8 orange orange 2 9 orange orange 2 10 yes cherry red 3

I have a macro that (1)copies a sheet, (2)renames them based on a list and (3)inserts them to the very end of the workbook. The copied sheets have a formula that draw data from other sheets. Sometimes the formula will calculate a 0 (zero) value in cell G62. For each copied sheet that has a 0 value, I want a macro that will go through and delete them.

All of the copied sheets are inserted after a sheet called "TOI." I don't know how to make a macro to delete a sheet based on a calculated cell value let alone tell it to stop at a certain referenced sheet (the "TOI" sheet).

Can you anyone help?

Thanks in advance.

VB:to look for the 4 characters and then add the zero, but it doesn't look like I can use wildcards or maybe my function is wrong. Any suggestions?(A1="?:??",0&A1,A1)If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have a large Excel file which containts multiple sheets. Excluding a few summary sheets, all the remaining sheets are identical in terms of formula and layout and are for different customers. I need to find a macro which allows me to hide a row if value in cell e.g., B10 is zero. I need to do so for a range of rows on each of these identical sheets (e.g, rows 10 to 50 and hide zero value rows if the correspondending values in column B is zero. E.g., hide row 10 if cell B10 is zero, hide row 11 if cell B11 is zero, etc. until hide row 50 if cell B50 is zero).

I will then need the macro to be applied to a range of sheets in the notebook from sheets e.g. from RE0001 to RE0100.

Your help will be much appreciated.

=OR(N4>0,O4>0,P4>0,Q4>0,R4>0,L4>0,Q4>0,K4>0)

How can I modify this formula so that the whole formula doesn't error out if one of the cells has #VALUE! in it?

I'm not sure if what I'd like to do can be done with a simple function

or if I can explain clearly what I'm trying to do.

I have a column (A1:A10) of totals derived from the rows that intersect

them (i.e. A1 contains =sum(B1:M1), A2 contains =sum(B2:M2), etc.) Only

A1:A6 contain non-zero values, so far.

I am calculating the MIN, MAX, and AVERAGE of the A1:A10 column. There

are two MIN issues I need to resolve:

1) Find the MIN(A1:A10) excluding zero values. I found this solution

in another posting: =MIN(IF(A1:A10=0,"",A1:A10)) and press ctrl + shift

+ enter. This works fine. This solution may not be needed if it can be

incorporated into the solution for the second part.

2) The zero values in A1:A10 will always be at the bottom of the list

until the corresponding rows are populated a row at a time descending.

So, with A1:A6 containing non-zero values, I'd like to exclude from the

MIN function not only the zero values A7:A10, but A6 also (the last

non-zero cell.) Is there a way to simply find the first zero value

cell in A1:A10 (let's say A7) and exclude A6 (A7-1) through A10 from

the MIN function on A1:A10? Does this make sense?

The reason for this is A7 remains a zero value until row 6 is fully

populated and row 7 gets its first piece of data. When A7 has a

non-zero value, this means that row 6 is now fully populated and should

be considered in the MIN(A1:10) function. At this point A7 should be

excluded even though it is no longer a non-zero value because row 7 in

not fully populated yet.

Any help would be appreciated.

Thanks,

--Robert-->

--

WeatherGuy

------------------------------------------------------------------------

WeatherGuy's Profile: http://www.excelforum.com/member.php...fo&userid=9254

View this thread: http://www.excelforum.com/showthread...hreadid=497216

No luck finding an answer? You could always try Google.