# Average Column With Positive Or Negative Number - Ignoring Zero And Blanks

Dec 5, 2011

I have a column of numbers that are derived with a formula. I need to Average only the ones that either have a Positive or Negative number, ignoring blanks or zero.

I have tried Search but couldn't find anything that address both blank and zero.

Sheet2

K610.00%624.76%632.53%6418.75%65666.38%6768-4.00%6970-5.84%710.86%

Excel tables to the web : [URL] .......

With this small sample, the answer should be 3.35% according to Excel when I choose just those neg and pos cells.

## Carrying Over Negative Number To Next Column As Positive Number?

Aug 21, 2014

I go negative in one column I want it to show up in the next column as a positive number because when I add my income and subtract my bills and the negative from last month to get the what cash I'll have left it screws up because negative subtract a negative is a positive. So how do i get the sum of say B9 to from -\$5.00 to +\$5.00 into C4 so when i subtract C4 From my income it wont add to it.

## AVERAGE Without Range Ignoring Blanks

Jan 4, 2009

Trying to determine the best way to do this. I understand that the standard AVERAGE function will ignore blanks if given a range; the function I'm using does a search for a particular value to determine if a value is to be included in the averaging: ...

## Average Mix Of Positive & Negative Numbers Separately

Aug 6, 2009

I have a column which is populated with profits and losses from a trading account. Here is an example:

Column I
400
200
-100
-150
-50

What I want to do is average all the profits, and average all the losses so I have two numbers - the average winner and the average loser. Continuing with the example from the 5 trades above, the average winner is 300 and the average loser is -100. How can I do this in excel?

## Excel 2010 :: Finding Average Value For Positive And Negative Set Of Numbers

Aug 25, 2014

Please find the attached MS Excel 2010 file <average set.xlsx>.

There is set of positive set & negative set of values available in the Column A. The values are plotted against Column A in Column B. Light green are positive sets and light yellow are negative sets.

Now I want to calculate the average for the positive set & negative set of values as shown in light majenta in the cells F4:F9. Also all majenta cells to be plotted by formula.

The Column H and Column I also same as like above , but here negative set of numbers starts first.

## Sequentially Number List Ignoring Blanks?

Feb 7, 2014

I have a column of week numbers but some gaps in the list (e.g. the job is complete and so I do not want to reference it) I am trying to create a list of jobs by week number. I need to sequentially number jobs to then use Vlookup e.g. job1-week1 job2-week1 etc to display be week.

I can work out to number the list ignoring the blanks but then resetting to 1 with each new week?

Workbook1.xlsx

## Convert Negative Number To Positive?

Nov 12, 2012

I keep getting the correct answer, but it returns it as negative and I need a positive answer.

## Total All Negative And All Positive Number

Apr 3, 2014

Here it is i needed to get the total of all negative and positive value.

## Transferring A Number (positive To Negative)

Oct 25, 2007

In one column, I have a formula that will produce a positive or negative number. If the number is negative, I want it to be automatically transfered to the adjacent cell/column. I don't want a positive number transfered though.

## Array Formula Not Ignoring Blanks To Count Row Number?

Oct 3, 2012

I have this array formula

=IF(ROWS(A\$11:A13)1,IF(MSB!\$A13"",SMALL(ROW(Table1[APP DATE])-ROW(MSB!\$A\$11)+1,ROWS(A\$11:A13)),""))))

the problem is that when both if statements are true i want it to get the row number, this is because table1 has blank rows (can not be avoided as the data is linked from a closed workbook)

i can see this works up until it meets the first blank row and i get #value error.

on table1 data exists on row 1,2,5 and 8 so my problem is getting the small function k to report these numbers?

## Positive To Negative Number And Vice Versa

Aug 22, 2008

I need to have a formula or code so that when a number is entered in cell E12 or F12 or L12, or M12 would treat a positive number as a negative and a negative number entered would be a positive in that respective cell.

## Number To Change Color For Positive & Negative

Dec 6, 2007

I am wanting to finish a worksheet for my boss. What I have done so far is taken the " forecast" of vehicles shown and used a formula of subtract from "actual" vehicles sold to get "how many we have left to go". what he wants to do is have the "to go" number change from red when a negative to green when it reaches a positive. example:

forecast = 30
actual = 27
to go = -3

that "to go" needs to be without a negative sign but in RED to negate a negative. Is that possible with a formula??

## Extract Negative & Positive Number From String

Jan 15, 2008

I am using a regular expression in a function to extract numbers from strings. the code I am using is below.

Function ExtractNumber(rCell As String)

Set RegEx = CreateObject("vbscript.regexp")
RegEx.Pattern = "d*.d*"
If RegEx.Test(rCell) Then
ExtractNumber = CDbl(RegEx.Execute(rCell)(0))
Else
ExtractNumber = vbNullString
End If

End Function...

## Conditional Formatting Highlights Same Number Even If Positive / Negative

May 29, 2013

Can I use Conditional formatting (highlights duplicate values) but highlight the number even if the number is an Positive or Negative number.

It must highlight the number if it's -300 or 300 in both instances.

## Count Max Number Of Consecutive Positive / Negative Numbers

May 2, 2007

Let's say I have a column A with the following values.

30
40
30
60
-20
-10
-50
-60
-70
120
320
20
-40
-30
40

How can I have 2 cells display:-

i)highest streak of positive numbers = 4
ii)highest streak of negative numbers = 5?

Also, how can I have another 2 cells display:-

iii)the sum of the highest streak of positive numbers = 160
iv)the sum of the highest streak of negative numbers = -210

## Conditional Formula To Convert Number With Positive Value To Its Negative Equivalent

Aug 27, 2012

I am looking for a conditional formula that can be applied to a cell so that when I enter a number with a positive value, it:

1) Converts it to its negative equivalent value.
2) Displays in the value in the cell with parentheses around the number to indicate that the value is negative.

I am trying to do this without having to reference another cell to provide the entry or display the outcome.

## How To Change Positive Into Negative Number Inside Cell Formula

Feb 7, 2014

How do I change a Positive number into a Negative number inside a cell formula?

I have a figure in Cell AE 101 which has the positive number 141

I have another cell W1 which has the positive number of 437

I want to create a formula in cell X1 that takes the number in cell AE 101 of 141 and convert the number inside the formula of 141 into a negative number of -141 and then subtracts that number from Cell W1 of 437 leaving an equation inside the formula to read. 437-141 = 296

Since the number in Cell W1 changes all the time and the number in AE101 Changes all the time the new formulated number in X1 will change all the time too as the cells get updated.

## Excel Vba To Copy Positive Number And Paste Negative On Cell Condition

Dec 4, 2013

I am using the following code to update an invoice summary sheet from an invoice sheet but when a Credit Note is issued I would like the Nett� value copied across to be a negative figure. When a credit note is issued the value of Service Invoice Cell L2 = 2 and an invoice L2 =1.

VB:
' Updates Summary Sheet with Invoice Details
Dim InvoiceDate As Date, InvNo As Integer, JobNo As Integer, Company As String, Location As String, Nett� As Currency

[Code].....

## CountIf Function On Positive And Negative Number In Attached Excel Sheet?

Apr 22, 2014

count if fuction on positive and negative number in the attached excel sheet. I would like to count how many numbers are positive and negative in the A column of the attached excel sheet.

## Highlight Positive And Negative Numbers Based On Data From Another Column?

Aug 4, 2014

Abc500
abc-500
bbb300
bbb250
cac100
cac-100
ddd200
ddd300
fff-500

I want to be able to highlight data in column B where amount is identical (ignore negative sign) only where data in column A is identical.

In this particular case it should highlight 500,-500 where data in column A = ABC & also 100,-100 where data in column A =CAC.

## Find Average When Only One Positive Number Is Available

Jun 29, 2006

I am trying to find a formula that works out the average of the figures in column K.....my problem is that so far in column K there is only one positive number (11)- the rest of the cells are DIV/0 so the average is showing as DIV/0. This is incorrect as the average is really 11 - how can I sort this out with a formula?

## Positive To Negative If Cell On Left Negative

Sep 1, 2007

I have data starting in E7. I want it to go down the column and find the negative numbers. If it finds one then I want it to change the number in the row to the left of it to a negative. So if E67 is a negative number, make D67 a negative and so forth down the line Sounds "simple" but how do I do it?

## If Number Is Positive Display In D20 - If Number Is Negative Display In D19?

May 12, 2014

I'm trying to create a completion statement on where I will add up the money my client has sent to me and also add up the money I need to pay out in order for them to complete their transaction. The end result I want is I would like to display whether my client has given me to much money and I need to repay them. Or if they haven't given me enough then I need to invoice them. I need excel to display for me after all the figures have been added and subtracted if the end figure is negative then display in D19 (balanced owed to you). If the end figure is positive (Balance required from you) display in D20.

## Calculating Column Average Excluding Blanks?

Apr 26, 2012

Project: x
Activity: xActifity TypeReference FormDec-11Jan-12Mar-12Apr-12May-12Feb-12Mar-12W/EW/EW/E2-Mar9-Mar16-Mar23-Mar30-Mar6-Apr13-Apr20-Apr27-Apr4-May11-May18-May25-May77%91%91%R/S95%100%100%R/S100%100%100%100%MMMM100%71%71%R/S100%100%R/SMMMM97%81%100%R/S100%MM100%R/S75%85%R/SR/SMM100%100%96%Average

Basically what I want to achieve is the average % of Dec 11 (D11:D22 on the sheet im working on) scores but excluding any blank fields (not excluding potential scores of 0%)

## Gathering The Sum Of Negative Numbers & Positive Numbers In A Column

Dec 17, 2009

I have a column of variances, these contain both negative numbers and positive numbers. I want to gather a sum of all the negative numbers and positive numbers separtely. Basically saying all the positive overeages = this amount And all the negative shortages = this amount. you can see the attached sample.

## Ignoring Blanks When Calculating SUM

Aug 12, 2014

I am using a simple SUM formula like this, =SUM(D10:E10). I would like for the SUM cell to be left blank until a value is entered into the cells D10. Currently it calculates the SUM as 0.

## SUM Last 6 Cells With Data (ignoring Blanks In Between)

Aug 8, 2014

I want to count back the last 6 cells in column S which has data (ignoring any blank cells) and add them up.

My data is ranging betwen S62:S143 (with S144 being the cell that I want to show the total for the last 6 cells with data). I have column headings and various other bits of data from S1:S61 that I want to exclude completely but need to keep for historical purposes.

Here is an example of some of the data in column S:

56
BLANK CELL
BLANK CELL
BLANK CELL
45
34
BLANK CELL
BLANK CELL
37
BLANK CELL
BLANK CELL
42
BLANK CELL
46
49
BLANK CELL
BLANK CELL
36
35

This is my formula so far:

=SUM(OFFSET(S62,0,0,COUNTA(S62:S143)))

I have tried adding a negative and positive number on the end of the COUNTA formula but all it does is count all the cells within that given heigh range, not a specific number of cells with data.

I don't specifically need to use SUM. As long as I get a total.

## Ignoring Negative Results In A Formula

Jun 9, 2009

Ignoring Negative results in a formula
Question withdrawn

## Matching Negative Value W/ Corresponding Positive Value

Jan 23, 2008

I have a spreadsheet of invoices and credits memos (500+ rows). Is there a way to match the positive and negatives that match? For example if there is -40.39 in one cell and positive 40.39 in another that it could highlight both those cells. I was thinking to do it in conditional formatting however I can not get it to work. It does not have to be fancy I just need to highlight them because there will not be a lot that do actually match.

## Convert Positive # To Negative

Mar 11, 2009

Is there a simple way to convert a positive number to a negative number ?

Fromto
10(10.00)
56(56.00)
116(116.00)

Additionally, would the same procedure work for time?
So if I had a column listing hours that looks like this:

10:05:00
4:34:00
2:18:06

is there a way to convert this to negative hours? Understandably, there are no such thing as negative hours, but I am trying to illustrate a situation where an internal procedure was completed x amount of hours before the specified start time.