• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filling numbers in between cells at irregular intervals

E

evilliv3

New Member
Hi all,

I have this problem at work and would appreciate any help. I am not good in VBA so anything but that will be great!


Here an example:

After Before

13.9 13.9

14.7

15.5

16.3 16.3

17

17.7

18.4

19.1

19.8 19.8

20.4

21

21.6

22.2

22.8

23.4

24

24.6

25.2 25.2

26.05

26.9

27.75

28.6

29.45

30.3 30.3


I have a column of numbers at irregular intervals. I would like to fill in the cells with the proper increment value based on the cells that bound them.


For 13.9 to 16.3, the increment is 0.8, giving 13.9, 14.7, 15.5, 16.3.

Likewise for 16.3 to 19.8, the increment is 0.7, giving 16.3, 17, 17.7, 18.4, 19.1 and 19.8.


I have to do this for 1000 over cells and doing it manually is beyond me. Is it possible to make excel auto calculate the increment and fill in the cells based on the limiting/neighboring cells?


Hope my question is clear. Thanks in advance!!
 
Hi ,


Can you download this file and check ? I have used a helper column to get the final output.


http://speedy.sh/breBH/Book3.xlsx


Narayan
 
Hi, evilliv3!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Additionally to NARAYANK991's suggestion you can type in B3 and copy down as needed:

=B2+A3-A2


Regards!
 
Hi SirJB7,


Thanks for the information.


For my question, I cannot use your suggestion because A3 is an unknown. The only numbers I know are 13.9, 16.3, 19.8, 25.2 and 30.3. The numbers between these 5 numbers at irregular intervals are what I need to find.


And sadly, NARAYANK991's formula is too complicated for me but it was good effort.


Thanks both of you for helping. :)
 
Hi Narayan,


Unfortunately it doesn't work for me. The one you have done for me previously was an example and I thought could modify yours to fit mine. I have uploaded an excel which is the actual problem.


http://speedy.sh/YN3UG/Book4.xlsx


Thanks much.
 
Hi ,


I think you have missed something ; the formulae I have used are array formulae , to be entered using CTRL SHIFT ENTER ; you will see the formula enclosed in left and right curly brackets { and }.


Check out your file here :


http://speedy.sh/6TCNN/Book4.xlsx


Narayan
 
Hi,

You could also try the following formula.


Assumptions

1. Data (with the variable gaps) is in B1:B24 -- Adapt the range as needed

2. Data in B1:B24 is in ascending order


Copy the following formula to cell C1, and then copy down to additional rows:

=IF(B1>0, B1, MAX(B$1:B1) + (( ROW()-ROW(INDEX(B$1:B$24, MATCH(MAX(B$1:B1), B$1:B$24, 0)))) * ( (MIN(B2:B$24) - MAX(B$1:B1))/(MATCH(MIN(B2:B$24), B$1:B$24)-MATCH(MAX(B$1:B1), B$1:B$24)))))


Explanation:

The outermost IF statement takes the easy way out, by returning the value in column B if there is a value present.

The rest of the formula calculates the following:

LowValue + ((Distance from current row to LowValue) * ValueIncrement)

where ValueIncrement is

(HighValue - LowValue) / (PositionOfHighValue-PositionOfLowValue)


LowValue is calculated using MAX(B$1:B1)


Distance from current row to LowValue is calculated as ( ROW()-ROW(INDEX(B$1:B$24, MATCH(MAX(B$1:B1), B$1:B$24, 0))))


HighValue is calculated as MIN(B2:B$24)


PositionOfHighValue is calculated as MATCH(MIN(B2:B$24), B$1:B$24)


PositionOfLowValue is calculated as MATCH(MAX(B$1:B1), B$1:B$24)


Please note the use of absolute and relative references when adapting the formula for your data set.


Cheers,

Sajan.
 
You must log in or register to reply here.
Back
Top