I have a column with zeros, positive numbers, and negative numbers. I need to find the last (bottom) positive number in the column.
Ex:
Column A has -
$50
$0
-$10
$22
$0
-$14
I want a formula or function in B1 that would return $22. However if I add a new row under the -$14 which contains $37, the formula should then return $37.
I have been googling all night, and I can't make this work.
I've got numerical data in columns A through AF, starting in row 4. I need a formula, starting in AG4 that I can drag down. I need the formula to look back across the row until it gets to the last posistive number, then return the column header of that column (the column headers are in row 3)
so for example
Header row
Jan Feb Mar Apr May Jun Jul Aug
Data
0 3 0 0 8 0 0 0
the formula would return May in this example as 8 is the las positive value and the column header for that column is 8. There are no blank cells in the data.
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.
I am trying to make a column show the result of a formula as positive number only. Right now the column subtracts two other cells and displays the result. I am trying to make it show all the results, positive or negative as positive.
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.
I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).
I know the column number (57) and want the formula to return the column name (BE) Auto Merged Post Until 24 Hrs Passes;not using vba that is! or if so , a custom function so macros dont have to be run.
I am creating a template in Excel 2010 and am having some issues due to a combination of positive and negative values. Specifically, I have a named range of values that can have either a positive or negative deviation from zero. I need to return the value that has the greatest deviation from zero (either + or -) based on criteria in another named range I have used the following array formula with success for the "Y" range: {=IF(PRB=M1,INDEX(Y,MATCH(MAX(ABS(Y)),ABS(Y),0)),MAX(Y))}
However, if there are not any negative values, as in the "Z" range, it only returns MAX(Z) for the entire range, when I would like ti to return "0.00134" (the largest deviation from zero based on the PRB range criterai. Would this be better accomplished using VBA?
I have a Range variable that points to a particular cell. If I wanted the address, I would do, cellName.Address. This returns, for example, "$F$3". If I wanted to have the column "F" as a number (column F would be the 6th column, so 6), how could I do that without storing some kind of array of letters and number equivilants. It would also have to work past the 26th column when the column addresses switch to, like, "AA", "AB" etc.
I have range like (3E:3K) in this range are only 2 different expressions. "Result and " forecast". I wanna look from left to right for the first cell with the string "forecast". And than I want to work with the column of this cell, only this column.
How do i do that?
Edited I corrected the coordinates. I want to look in a specific column line (Nr.3) from there the first Cell with "Forecast" and from that String the row number.
They following 4 posts were based on wrong informations by me.
i have a list of numbers in column A and B and in column C is = B - A. But what i want is for Column C to always = positive number regardless even if say A = 10 and B = 8 the true answer is -2 but i would like the reading of 2. Is there a way to say B - A and if = - make it -( ) ? (otherwords +)
i want should be done as a function or VBa. I would like to have A Column so that only postive numbers can be entered. Disallowing 0 and below from being entered into cells.
I need a formula that will look at a row (say row 1) starting in column F. When the first positive value appears in the row, the formula will then sum that value and the next 11 columns in the same row. So for example, starting in row 1 column F - here is an example list of values
0 0 0 0 3 4 3 2 0 4 8 7 9 0 9 0 6 4 2 2 0 8 7 The total would be 49 (start summing at the first 3 (which would be column J)and finish with the 0 (which would be column U))
Lets assume the formula is in column AZ, row 1 and I then need to drag it down
I am trying to see if there is a way using count or another option, of using one formula to look in a column and then spit out how many of each item are there:
i.e: lets say it says a, b, c, d etc...
Can a formula go through and say you have 3 of a, 5 of b and so on
I understand the basic of count sum iserror etc, just not sure if excel can do what I am wanting or if i should use access.
I have the following two rows that I'm looking to return a single column number that maxes two criteria. In the example, 70, 30 would return column 7. I'm not looking to create a dummy row the combines the two. Reason for using MATCH or HLOOKUP is that the two values may not be the values in the given table.
I am trying to somehow use multiple IF functions. I was able to use one IF function to disply '--' in a cell if a particular formula = 0. However I ran into a problem when negative values were displayed. I wanted the value to be a positive number. I thought I could use another IF function to multiply the formulas contents by -1 if it were <0, but I keep getting a #VALUE error in my cells. Is there a way I can have multiple conditions?
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.
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?
to create with the default excel functions the following calculator. I need to calculate the maximum number of positive numbers which happen in a row and the maximum number of consecutive negative numbers. For example in the following list of numbers there are a maximum of 7 consecutive positive and a maximum of 6 consecutive negative numbers:
i have 2 sheets one called "IS" and the other called "AS" in cell a2 of "IS" is a number that i need to have excel look up in column a of "AS" once it find that number i need it to return the number thats in column e of that row to sheet "IS"column d. summary: a2 of "IS" looked up on sheet "AS" and returns the number in column e to cell d2 of "IS"