Solved

Populate data based ona  criteria

Posted on 2016-12-01
6
18 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
  • 3
  • 3
6 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Closing Comment

by:Dokat
Comment Utility
Thank you for your help!
0
 

Author Comment

by:Dokat
Comment Utility
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)
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now