Mr Excel & excelisfun Trick 13: AVERAGE without Zeroes

Hi,
I saw EXCELISFUN TRICK 369. I need to do something similar. I tried to follow his code and couldn't. Then I tried copying it and growing his records and still couldn't get it to work.

What I am trying to do is on the first list use an inventory list. Which could be about 2000 - 5000 records or maybe double that (not sure how big of a list I could use in Excel). But lets say it is the max number (if someone could tell me that number I would be most appreciative).

I then will load a second list, or would load a list into second column. I then want the difference (what is missing) from the second list to appear in the third list. If it can give me the row of where it is in the first list that would be great (not a problem if you can't). I just don't know why the code from that video is not working any help would be greatly aprreciated?

Thanks,

Peter Fraga
(fragapete@hotmail.com)

Hi All,

I was setting up a spreadsheet that was based on the following vid:
http://www.youtube.com/user/ExcelIsF...14/tqCEY5YMyqw

Dynamic sub tables based on a master sheet array formula

The formula in question is:
=IF(ROWS(A$7:A7)>$E$1,"",INDEX('2010Corn'!A$4:A$17,SMALL(IF(Table2[From]=$B$1,ROW(Table2[From])-ROW('2010Corn'!$H$4)+1),ROWS(A$7:A7))))

B1 is the customer I'm looking for, E1 is the count for the customer and the master page is 2010Corn. I have 20 sheets looking to this master page for data. It works great, except for an issue when adding a new line in the master table.

What is happening is when I get to the end of a row, I tab to enter a new line in the table. It takes up to a minute for the cell to change color and for me to regain control of the computer.

I have run a performance trace and while the computer is locked, one of the CPU core's is pegged for the whole time with a processor que of up to 10 items at a time.

My question is...does anyone have any hints how to optimize this formula?

Thanks
Tony

Hello,

Lets assume :

Cells A1:A9 have the value 10
Cell A10 has the the value '0' (zero)

I wish to use the average function to get an average of A1:A10.

How can I exclude the ZERO?

AVERAGEA can exclude BLANKS but not zero.

Can anyone help?

Regards

Just This Video For Fun

http://www.youtube.com/watch?v=0VvjrldlqI0

Hiya,

I've been trying to get an averaging forumula to work with logical arguments.

I have all my data in a sheet called "Data", with Column C containing either "Occupied" or "Unoccupied"
Column A in the same sheet is the season "Summer", "Winter", etc
And Column F in the same sheet has the time of day.
The data I want to Average is in Column G.

On another sheet I have the hours of the day (midnight to 11:30pm) in Column A. In Column B of the same sheet I want it to average all the values in G on the Data sheet, with the matching time in Column A, if the variables in Columns A and C are "Summer" and "Occupied".

So, basically I'll have the average daily profile for days within the same season, and with the same occupancy condition.

This is what I've been trying to make work, and Excel keeps removing the ' marks around the references to the Data sheet and pinging back a blank cell with an error:

VB:

=If(And( 'Data'!$C$3:$C$35138="Occupied", 'Data'!$A$3:$A$35138="SUMMER", 'Data'!F15=A15), AVERAGE('Data'!$G$3:$G$35138), "")

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Can someone help?
Thanks!

Hi,

I have been grappling with this question for some time now wondering if anyone can help...

I am attempting to calculate an average percentage whos range will be moving one cell up every month upon update.

If you look at the attached spreadsheet, columns A and B start counting up from 0 as soon as a valid month is entered. (formula for this excluded)

I am using columns A and B, and row 3, in a SumProduct function in order to get the numerator of a calculation that will arrive at the average.

The sumproduct function is entered in rows 43 and 44.

The function is as follows for cell F43:

=(SUMPRODUCT(($A6:$A39<M3)*($B6:$B39>=G3)*(F6:F39)))/
MIN((M3-G3),(COUNTIF(F6:F39,">0")))


The numerator of this formula uses the adjusting numbers in columns A and B to always arrive at the sum of the last 6 months of data points. So for column F, I am receiving in the numerator the sum for data points for F12:F17, which is the range between the numbers 3 and 8.

My issue revolves around the denominator. To get an accurate average, I need to divide the numerator by 4. I am instead dividing it by 6. (MIN((M3-G3),(COUNTIF(F6:F39,">0"))). = MIN(6,10) (using Min and CountIf for a later issue where there are less than 6 datapoints available)

I am restricted to leaving the zeroes in the blank fields below the data points, as I cannot have a non-numeric field in a sumproduct range. (I have tried replacing the "0"'s with ""'s and " "'s in the formula that results in "0"'s being placed where there is a null)

So for cell F43 the average states 20.10%, what I need is the average to be 30.15% which is the last 6 data points, exluding the 2 zeroes in July and August...

Any suggestions on this would be great!

I am having trouble creating a similar spreadsheet to Excelisfun video #712. The only difference between mine and his, is that I have 3 blanks between the data sets. Can someone help?

http://www.youtube.com/user/ExcelIsF...22/Po35KC2ODNw

hi i have writtern the following in a comman button

sub trythis()
dim x as range
set x = range("b1:b20")
for each x in x
if x.text = "June" then
x.copy
end if
next x
end sub

I have in the range names of the month repeated randomly. June appears 6 times in the list

By executing the above code the last cell containing "June" gets into copy mode (the cell is filled with marchings ants effect). Although the loop goes through many cells containing "June" only the last cell containing "June" shows this effect, how extend this effect to all the cells containing the text "June".

I am following this tutorial from youtube. It is a search function to search for a specefic word in an entire row. Here is the first part of the code:

Code:

=SEARCH(C$3,Table2[[#This Row],[DESCRIPTION]])

except i only get a value in the cell that i typed this in, instead of it searching the rest of the row like in the tutorial. i can't figure out how to fix this. anyone know what im doing wrong?

Good morning,

trying to find out what to do with my spreadsheet and the formulas in it.

If you look into the file 369. I can get to the stage where it outlines the mismatch figure. In the example it is 6 Mismatches.

But I cannot seem to get the table belwo right. Everytime I try and copy past the formula, the first set of data is the one from the first line and then I get a #NUM error, due to the fact that there seems to be a number reoccurence somewhere.

I took the youtube tutorial, but even copying that formula did not work.

My table consist of 2 sets of 600 values each.

Can someone tell me why I am getting this error and help me resolve the issue that the result is an existing set of data?

THANK YOU

BayerStars23
[IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot.png[/IMG][IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot-1.png[/IMG]