Solved

Populate data based on a  criteria

Posted on 2016-12-01
2
39 Views
Last Modified: 2017-01-17
Hi,

Below is the table i am working with. i 'd like to populate data in cell c1, c2, c3, c4 based on a criteria. For example: If the criteria is 4 i want cells C1,C2,C3,C4 to display 70,80,90,100 respectively and not show anything in c5,c6.... if the criteria is 5 i want cells c1,c2,c3,c4,c5 to display, 60,70,80,90,100 and so on.

Is there a formula that can generate this result? Thanks

Column A Column B
Date             Sales
1/2/2016     11
1/9/2016     23
1/16/2016   304
1/23/2016   40
1/30/2016   50
2/7/2016    60
2/14/2016   70
2/21/2016   80
2/28/2016   90
3/7/2016    100
0
Comment
Question by:Dokat
2 Comments
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 41909685
=IF(ISBLANK(INDEX(B:B, COUNTA(A:A)-G$1+ROW())), "", INDEX(B:B, COUNTA(A:A)-G$1+ROW()))
0
 

Author Comment

by:Dokat
ID: 41909696
Thank you Wayne! it worked i just had to add -1 to the formula

IF(ISBLANK(INDEX($B$2:$B$11, COUNTA($A$2:$A$11)-G$1-1+ROW())), "", INDEX($B$2:$B$11, COUNTA($A$2:$A$11)-G$1-1+ROW()))
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question