Solved

Populate data based ona  criteria

Posted on 2016-12-01
6
57 Views
Last Modified: 2016-12-01
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 11,23,304,40 respectively and not show anything in c5,c6.... 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41909462
So you want only the first X number of records listed in column C?

Assuming the table headers are in Row 1 and the criteria is in cell G1, you can use this formula copied down as required....

=IF(ROW()<G$1+2, B2, "")
0
 

Author Comment

by:Dokat
ID: 41909477
Hi Wayne,

Thanks for your response. This formula only populate if the row number is greater than G1+2. what if it is less than G1+2?

Thanks for your response?
0
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 41909494
This formula only populate if the row number is greater than G1+2. what if it is less than G1+2?

No, "<" = less than. For example, if G1 = 4, rows 2 to 5 will be shown. If G1 = 15, rows 2 to 16 will be shown.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:Dokat
ID: 41909652
Thank you for your help!
0
 

Author Comment

by:Dokat
ID: 41909655
Your previous solution worked. however what if i want cells to display C1,C2,C3,C4 to display 70,80,90,100 respectively when G1=4 or C1,C2,C3,C4,C5 to display 60,70,80,90,100? basically last 4 or 4 rows of data rather than the first 4 or 5 rows?

Thank you so much for your help
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41909684
A bit more complicated, but this formula in cell C1 copied down will give what you request...

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

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

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…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

752 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