Return The Nearest Cell Above That Isn't 0

Dec 17, 2009

I have a list in column A that goes [Blue,0,0,0,Green,0,0,Orange,0,0,0,0,0, etc.] The number of 0s is not in any pattern. Based on column A, I would like column B to go [Blue, Blue, Blue, Blue, Green, Green, Green, Orange, Orange, Orange, Orange, Orange, Orange,etc.].

So, I would like column B to look at column A, and if it is a 0, return the nearest color above. I was thinking of maybe using a whole bunch of imbedded "if" functions that would keep going up and up if it was 0 and then return the cell when it's not 0, but sometimes it might be 30 0s, so I'm not sure that's the most efficient way to go about it (or maybe it is and I'm not thinking about it right). Does anyone know a nice neat little function that would work? I've attached a sample spreadsheet that illustrates what I'm trying to do. Column A is what I start with and Column C is where I'd like to be.

View 2 Replies


ADVERTISEMENT

Excel Function To Return Nearest Non Blank Cell Value From Rectangular Array Of Cells

Jan 10, 2014

Assume you have a rectangular range. Say A1:M18. There are values in some random cells (any number of cells) within the range. The rest of cells are blank. In cell M19 (ie cell below the bottom right corner of the range), I need a excel function that returns the nearest (ie physical location) non blank cell value. I have searched the internet for weeks. The closest formula is the following (see below) but it is flawed as it returns zero if there are values in rows below the row of the nearest cell with non blank...and also zero if there is value in a column greater than the column of the nearest cell with non blank.

Closest formula so far:
INDEX(A1:M18,LARGE(IF(ISBLANK(A1:M18),"",ROW(A1:M18)),1)-ROW(A1:M18)+1,LARGE(IF(ISBLANK(A1:M18),"",COLUMN(A1:M18)),1)-COLUMN(A1:M18)+1).

And the function should be relative not absolute as the range can be extended..but the location of the formula remain same relative to the array size. (ie. always at below the right bottom cell).

NOTE: Ignore the special case where two non blank cells are the nearest. That will not happen.

View 5 Replies View Related

Using VBA To Find Exact Or Nearest Match And Return From Data Sheets

May 15, 2014

I has number sheets with thousands rows of unsort data. I need to find the price, with optional name and date if given, to return the rows values.

Example from Summary sheet, to find the price range and return 3 rows (even there are four set of answers, highlighted in light blue), with sorting the highest price first.

Summary Sheet
Sheet name
S01
S02
S03
S04
S05
S06
S07

Product
1
2
3
4
5
6
7

Search Fields

[Code] ........

View 1 Replies View Related

Acquiring Value Of The Nearest Cell

Mar 14, 2007

I've made dropdownboxes with choices out of X,Y and Z..
And when I choose "Y" it must acquire the value of the nearest cell in the search range.

For example when I choose "Y" in "H35" then "H36" must get its value of "F32" (see attachment)
I've used the following code in H36:

= LOOKUP("A";E$3:F36)

Now H36 doesnt acquire the value of F32; but the value of F20...

View 6 Replies View Related

Find Nearest Match To The Number In Cell

Dec 11, 2013

In cell A1 I have a number. I want a formula that looks at that number and then looks at row C on sheet2 where there are rows and rows of numbers. I want the formula to return the nearest match to the number in cell A1.

View 6 Replies View Related

Can Make A Cell Round Value To Nearest Whole Number?

Dec 10, 2012

I have a cell that is going to be used for the quantity of a certain item. I would like to dummy proof this sheet as much as possible so that if someone puts in 21.5, that it will do something like change the value to either 21 or 22, since there can't really be a .5 of this item.

View 5 Replies View Related

Discount Applied To A Cell And Rounded To Nearest Nine

Jul 10, 2014

I want to take the value in I and apply a 10% discount to it and place the new value in column M. Would it be possible to have this new value rounded up or down to the nearest 9.

Example:
Column I=7.99
Column M=7.19

Column I=7.59
Column M=6.83 (however I would need it to be rounded to the nearest 9. The price I would need would be 6.79)

Column I=2.19
Column M=1.97 (however I would need it to be rounded to the nearest 9. The price I would need would be 1.99)

View 3 Replies View Related

Entering Data In Cell And Having It Round To The Nearest .25

Sep 24, 2007

Is it possible to have this function work to round in the same cell the data is entered in? ...

View 9 Replies View Related

Round To Nearest 15 Then Nearest Hour If Less Than 1 Hour

Apr 4, 2014

I have a form to round to nearest quarter but if it is less than 1 hour I need it to round to a total of 1. Can this be combined in one formula.

I also need my time to be configured so that if the start time is a PM number then end time AM it does not figure right. is there a way to remove the AM/PM from time. I have already tried all the formats from number,time, & custom.

Attached is my form : Timesheet Form 2014.xlsx

View 4 Replies View Related

How To Return Address Of Column Or Cell Select Not Just Return Value

Aug 18, 2014

How to return address of the column or cell I selected from Application.Inputbox, not just return the value?

[Code] ....

View 8 Replies View Related

Formula: If Value Return Is = To Value Of Cell Above Then Find Return Next Value

Feb 17, 2010

Is there a way with the following formula to tell it that if value return is = to value of cell above then find return next value?

View 6 Replies View Related

Round To The Nearest 5

Feb 20, 2009

I am using the rounding function in excel and its rounding to the 10's, is there a way to round to the nearest 5?

View 4 Replies View Related

Roundup To Nearest 50?

Apr 29, 2013

I need a formula that will roundup the amount in a cell to the nearest 50 (as in 246 £ will become 250 £)

View 4 Replies View Related

Round To Nearest .05 Or .09

Feb 6, 2010

Below is my current formula. Right now I have it rounding the results to the nearest .09. I would also like to have it round to the nearest .05 as well.

In other words, to be more exact: I need all numbers that end in 0 or 1 to be rounded down to 9; any numbers that end in 7 or 8 to be rounded up to 9; any numbers that end in 2, 3, or 4 to be rounded up to 5; and 6 to be rounded down to 5. And, of course any numbers ending in 5 or 9 shouldn't change.

View 14 Replies View Related

Round Up To Nearest Whole 50?

May 23, 2012

I am using the MROUND function and it will round up or down to the nearest whole 50. Is there a function to round only UP to the nearest whole 50?

ie: 1704 is being rounded to 1700 I need 1704 to rounded to 1750

View 3 Replies View Related

Rounding To The Nearest 0 Or 5.

Mar 11, 2009

I have excel 2003. I'm working on a weight lifting program and need to have cells round to the nearest 0 or 5. My problem is that the cells I need to round already have a formula in them and I can't get the cell to round the answer of the formula.

D17=U4*.6

I need the answer to be rounded to the nearest 0 or 5.

View 9 Replies View Related

Rounding To Nearest X

Nov 2, 2006

I wish to apply a rounding rule that will correctly round up or down to the nearest 5 or 0.

I tried using ceiling and floor and am almost there, but not quite.

Rule is to round up or down to nearest 5 or 0

originalplancfcheckroundedrulewill use
634should be 630635630f634
634.4should be 630635630f6343f
634.5should be 635635630c6352c
634.6should be 635635630c6352c
635should be 635635635c Or f6351f
635.1should be 635640635f6351f
635.5should be 640640635c6364c ...............

View 9 Replies View Related

Round Up To Nearest X`

Jun 9, 2007

I trying to make a formula that will multiply a cell by 1.43 and then round the total up to the nearest 45 or 95 with no decimal points.

View 9 Replies View Related

Rounding To Nearest 15 Minutes?

Jun 28, 2012

I have downloaded a punch in time clock from another user " Alex17", great job by the way. I was wondering on how to apply some certain rules this. I would need the times to round to the nearest quarter. Let's say someone punched in @8:01AM or any time up to 8:07AM, I would need it to round to 8:00AM, if they punched in from 8:08AM up to anytime to 8:14Am, I would need that to round to 8:15AM or if someone punched in @ 8:23AM it would round to 8:30AM....etc. I attached the form.

I need these rules to apply

7:00 - 7:07 round down to 7
7:08 - 7:15 round up to 7:15
7:16 - 7:22 round down to 7:15
7:23 - 7:30 round up to 7:30
7:31 - 7:37 round down to 7:30
7:38 - 7:45 round up to 7:45
7:46 - 7:52 round down to 7:45
7:53 - 8:00 round up to 8

or if this makes more sense

7:00 - 7:07 round down to 7
7:08 - 7:15 round up to 7.25
7:16 - 7:22 round down to 7.25
7:23 - 7:30 round up to 7.5
7:31 - 7:37 round down to 7.5
7:38 - 7:45 round up to 7.75
7:46 - 7:52 round down to 7.75
7:53 - 8:00 round up to 8

View 4 Replies View Related

Round To Nearest 0.99 If Value Is Greater Than 0.95 Or Less Than 0.05

Dec 12, 2013

I am looking for a formula which will allow a value to round up or down to the nearest x.99 if the value is greater than x.95 or less x.05 otherwise remain the same. i.e if the value is 0.96 it should read 0.99 and if the value is 1.04 it should read 0.99. however if the value 1.05 it should read 1.05.

View 9 Replies View Related

Select Nearest Date After Specified

Jan 30, 2014

was given only looked at the nearest date and not the nearest date after.

So here goes; I have a table that looks at new starters and the date they started, and then a list of dates appointments offered (these dates can and often do precede to person joining us).

What I'm looking for the first chronological date AFTER the New start Date.

In row 3 this works; as it is the nearest date, but in row 4 however, it is the nearest date; but occurs before the New start Date, thus is incorrect.

View 2 Replies View Related

Pick Nearest Data?

May 18, 2009

If I have a date that is 12 February 2008 I need to pick up data next to 31 January 2008 as that is nearest, however if my date is >15th February 2008 I want to pick up the data in the column next to 29 February 2008......

View 2 Replies View Related

Round Number To Nearest .05

Feb 18, 2013

I need to round numbers up in a spreadsheet to the nearest .05 . For example, I would need to change 1.502 to 1.55 or 1.556 to 1.60 .

View 3 Replies View Related

Rounding Off To He Nearest Fraction

Sep 18, 2009

I write up job cards that require measurements in fraction form. Usually whenever i want to round off a figure , say 10/16, to 5/8, i right click on the figure and format cell it. However, this can be quite time consuming if there are multiple cells and are of a different breakdown.

What i would like to know if there's a shortcut way where when the 10/16 is in the cell, it automatically rounds off to the nearest fraction.

View 9 Replies View Related

Round Function Nearest .5

Oct 30, 2009

I'm trying to round some figures UP to the nearest .5, However i only know this formula: =MROUND(P2,0.5) and that rounds to the nearest .5? Is there anything i can use instead to make sure it is always up not down. so 6.13 becomes 6.50 or 7.53 become 8.00? etc.

View 2 Replies View Related

Rounding To Nearest Dollar

Oct 16, 2007

How do I round $452.57 in cell A1 to $453.00? I want to format the cells to round to the nearest dollar and leave the .00 cents.

View 9 Replies View Related

Round To The Nearest Work Day

Dec 3, 2007

How do I format the date to be to the nearest work day?

I want to establish a due date for an asset that is due 5 days before launch. If my launch date is in A1, and my due date is in A2, in A2 I put:

=A1-5

So how do I make sure that the resulting date is a workday?

View 9 Replies View Related

Rounding To The Nearest 5 (VB Code)

Dec 31, 2007

In cell A1, I might have the following numbers: 1, 5, 10, 50, 100.
In cell B1, I will have an unknown number
In cell C1, I want to round the number in B1 using the information given in A1.

For example:
A1 = 1
B1 = 153.45
C1 should be 153
C1 = round(B1,0)

A1 = 5
B1 = 153.45
C1 should be 155
C1 = round(B1,0) +/- 5 (depending on the situation)

A1 = 10
B1 = 153.45
C1 should be 150
C1 = round(B1,-1)

A1 = 50
B1 = 153.45
C1 should be 150
C1 = round(B1,-1) +/- 50 (depending on the situation)

Is there a quick of doing this using VB? I can do a bunch of if then statements... but was wondering if there's an easier way.

View 9 Replies View Related

Nearest Neighbour Algorithm With Vba

Feb 5, 2008

I am trying to solve the nearest neighbour algorithm with vba

For example, I have a table as the following

0 1 2 3 4 5 1
10
5
15
20
2 10

8
9
7
3 5...........

View 9 Replies View Related

Nearest Date In The Dataset

Oct 20, 2008

I need to capture the first date that the data appears for a long list of SKUs. I've downloaded the data to have SKUs in a row and dates in the column (consumed over 200 columns).

Would there be a systematic way to generate the first date the data appears instead of having to look at each row, get to the nearest data by using shirt right arrow and then manually type in the corresponding date?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved