Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Populate data based on a  criteria

Posted on 2016-12-01
Medium Priority
65 Views
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
Question by:Dokat

LVL 48

Accepted Solution

Wayne Taylor (webtubbs) earned 2000 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

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

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month11 days, 14 hours left to enroll