•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
auto populate cells with values based on a known value that falls on or between data from 2 columns
Our take
Are you tired of manually populating cells in your spreadsheet based on known values? If you want to auto-populate cells B22 and C22 using values from row 6, there's a more efficient way. By leveraging a formula, you can automatically fill these cells based on the constraints of your known value in B12. Instead of searching for the right values and entering them by hand, discover how a simple formula can streamline this process, saving you time and reducing errors. Let’s explore this solution together.
i want to auto-populate 2 cells based on constraints related to a known value.
Using the below data:
I want to populate cells B22 and C22 with values from row 6, based on the value in cell B12.
For example, if B12 is 0.2, I would like to populate B22 with 5000 and C22 with 10000 as 0.2 falls somewhere between those 2 columns in my data set.
Using =forecast.linear, i've already got the calculation to determine the exact value between the 5000 and 10000, but i currently have to find which 2 columns my known value falls between and then manually enter the numbers into B22 and C22.
I was hoping there was a statement that would populate the cells for me.
Thanks
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- What formula should I be using to populate one column’s data automatically based on my entry for another column?Hi! I need some help here. first off, I’m not allowed to use a v-lookup for this file due to some organisational restrictions, so i’m unable to create a table 2 to facilitate the lookup values. I’m only allowed to use table 1 itself, which is where I have to put in all my values. I have been trying to use index/match instead but can’t see to figure the right formula out. so basically, what I need is for my manual input of data in column J to populate the associated remark in column K. • column J input “NA” = column K input “visit 2” • column J input “nil” = column K input “insufficient blood” • column J input “1” = column K input blank these are the only 3 that I require. let’s say I require it for 30 rows. so my range of rows to populate would be K2:K30 based on the directly adjacent data of J2:J30. how can I do this in formula or automation? thanks in advance! submitted by /u/yanny-jo [link] [comments]
- Populate Cells From a Random Range of Cells, Based on the Contents of Different Cells.Hello Reddit pros, I am trying to create/find a formula that will do the following: Check if A2 matches values in D1:D6 and return a random value from E1:E6 into B2. The hard part is this; if A2 equals D1:D3 then the random value of B2 is from E1:E3, but if A2 equals D4:D6 then the random value of B2 is from E4:E6. For example: if A2 says Input 5, then B2 could be either Result D, Result E, or Result F. The values in the screenshot are just for demonstration; I will be able to extrapolate what I learn onto the spreadsheet I need it for. I am just curious if there is a formula to do that, or am I just having a lot of high hopes? I have tried using IF combined with INDEX and RANDBETWEEN but I cannot seem to get the formula correct for doing even the first part of what I need, let alone the second part. It looks like this: =IF(A2:A19,INDEX(D1:D3,RANDBETWEEN(1,COUNTA(E1:E3))),"") This obviously is not correct, and it returns a #VALUE error that I cannot figure out. I do not know the correct way to phrase the question to get a viable answer via internet searching, so I am once again turning to the experts on Reddit. Thanks for any insight. I am aware the formula on the screenshot is different than my post body; I deleted the top row but didn't fix the formula u/Connect_Camel_5998 solved it for me. Thanks everybody! The formula that was posted works great for what I needed! submitted by /u/ClandestineGhost [link] [comments]
- How to autopopulate according to certain valueshi i need help 🙏🏼 which formula to use for below if Value is 1112, 1212, 1312, 1412 then its A if Value is 2134, 2234, 2334, 2434 then its B if Value is 3156, 3256, 3356, 3456 then its C so lets say that the value is 2234, i want the formula to input as B if the value is 1112, the formula will input as A submitted by /u/anonymous_12629 [link] [comments]
- How to autofill data from a row to a column on a different sheet in the same folder?I've been struggling with some solutions I've found on the forum but after 1.5hrs I'm close to giving up and manually entering data - which is bound to cost me another 28 hrs. Hoping someone has the solution I'm looking for and is willing to share.. I've exported questionnaire results from Mentimeter to Excel. The document output is formatted automatically in a way that uses columns for unique respondents, followed by their answers in the same column but along individual cells on that column's row, meaning the first entry is A2 and the last entry is in cell CQ2 or something. I would like to make this more user-friendly by: 1) putting each respondent's answers in their own sheet in the folder, and 2) by listing the questions in the first column and the answers in the next columns pretty much 'the other way around'. Currently it looks like this; the answers I need are listed in !VotersF3 to !VotersCQ3. The next respondent's answers are in !VotersF4 through to !VotersCQ4 and so on. What I'm looking for would ideally display answers in !AnswerA3 through to A80. When I manually select !AnswerA3 and click on !VotersF3, logically it does what I want. When I then drag down to autofill, equally logically the sheet enters !VotersF4 instead of !VotersB3 as it's a row vs column problem. I've tried different version of INDEX and TRANSPOSE but I can't get a working formula from that. Would anyone be able to provide me with the correct solution for doing this? I've got another 20+ respondents answers that need to be 'easy to view' instead of scrolling 500 screens horizontally.... Thank you Excel wizards! :) submitted by /u/Lost_Mud2097 [link] [comments]