- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Coaching Changes
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
re: Excel help request
Posted on 2/16/20 at 2:16 pm to GeneralLee
Posted on 2/16/20 at 2:16 pm to GeneralLee
This is a formula copied from a Google sheet tracking weight loss. Not sure if this will work, and I don't have access to excel to test it:
=IF(ISBLANK(A192), "", AverageIF(A$5:A192, ">"&(A192-7), B$5:B192))
the if conditions whether other cells in the row display, based on the column A value being filled in.
ISBLANK is the one that might hold a clue. Wonder if there is an opposite to ISBLANK, or a NOT ISBLANK argument.
=IF(ISBLANK(A192), "", AverageIF(A$5:A192, ">"&(A192-7), B$5:B192))
the if conditions whether other cells in the row display, based on the column A value being filled in.
ISBLANK is the one that might hold a clue. Wonder if there is an opposite to ISBLANK, or a NOT ISBLANK argument.
0
Posted on 2/16/20 at 2:17 pm to VermilionTiger
Sorry man, it's a beast of a file and I didn't do a good job explaining it in English, my bad. I think the vlookup function will work for the numeric columns, and the text columns are so few that I can just do twelve "If" formulas in each cell to pull the last text value.
Posted on 2/16/20 at 2:18 pm to GeneralLee
Are you looking to do a "+2" in the new column based on the value from the previous column cell?
=MAX(IF(ProNumber < 70000, ProNumber))+2
=MAX(IF(ProNumber < 70000, ProNumber))+2
This post was edited on 2/16/20 at 2:20 pm
Posted on 2/16/20 at 2:20 pm to GeneralLee
Not exactly following. If you have current month actuals, you want the year 6 projection to include the latest data?
Posted on 2/16/20 at 2:24 pm to pjab
My beginning month is February 2020, and I'm going out to December 2025 as that's the last month going out five years, finishing out the last year. But if my beginning month was November 2020, to get to December 2025 would be earlier up in the spreadsheet and I'd have 9 more rows of blank data to sift through to get the last numeric value to begin annual formulas for 2026.
Posted on 2/16/20 at 2:26 pm to GeneralLee
Sounds like two things happened
You boxed yourself into a corner
or
You had something going and you are now trying to expand it to something that your original spreadsheet wasn't intended for
You boxed yourself into a corner
or
You had something going and you are now trying to expand it to something that your original spreadsheet wasn't intended for
This post was edited on 2/16/20 at 2:27 pm
Posted on 2/16/20 at 2:27 pm to GeneralLee
quote:
For example, let's say I have five cells, and the numbers may be : 200, 202, 204, 206, and then a blank cell.
Assume these are in A1, A2, ... ,A5
B1 formula would be
=IF(A2=“”,B1,””)
Then copy down.
Posted on 2/16/20 at 2:30 pm to VermilionTiger
quote:
Sounds like two things happened
You boxed yourself into a corner
or
You had something going and you are now trying to expand it to something that your original spreadsheet wasn't intended for
Very close. I'm converting an Excel file into a website, but the website is much slower than the Excel file. I originally had 100 years of monthly data, but that was making the website way too slow. So now I'm switching to five years of monthly data and 95 years of annual data to speed up the website.
Posted on 2/16/20 at 2:31 pm to G2160
quote:
Assume these are in A1, A2, ... ,A5
B1 formula would be
=IF(A2=“”,B1,””)
Then copy down.
Yep, I have ~400 columns of numeric values that I'm using the vlookup function for, and then ~5 columns of text that I'm going to use the nested IF's formulas for.
Posted on 2/16/20 at 2:34 pm to GeneralLee
Hell yeah, man.
Sounds like a fun project. I'm currently working on a big company excel project and it's really challenging.. but really fun at the same time.
Sounds like a fun project. I'm currently working on a big company excel project and it's really challenging.. but really fun at the same time.
Posted on 2/16/20 at 2:39 pm to GeneralLee
IF(index(match, match)). As you actuals populate, the last year projection will populate over whatever formula you have.
Posted on 2/16/20 at 2:44 pm to GeneralLee
quote:
VBA is not an option unfortunately.
Dumb.
Can you do a Count or CountIf or CountA on the column and then use that number as your index? There's probably a simpler solution but I'm not at my computer.
Also why aren't the "empty" cells actually empty?
Posted on 2/16/20 at 2:51 pm to GeneralLee
Wouldn't the Max function work the =MAX(a1:b255)
Posted on 2/16/20 at 2:59 pm to pjab
The index function with Blank will be the solution. Combined with If and vlookup.
I’m just not in a work mode.
And OP, for that big of a dB, you should be using Tableau or something to present the data.
I’m just not in a work mode.
And OP, for that big of a dB, you should be using Tableau or something to present the data.
This post was edited on 2/16/20 at 3:03 pm
Posted on 2/16/20 at 3:01 pm to GeneralLee
Nerd
This post was edited on 2/16/20 at 3:02 pm
Posted on 2/16/20 at 3:06 pm to Bullfrog
quote:
And OP, for that big of a dB, you should be using Tableau or something to present the data.
Agreed. This is for a side business of mine I'm starting up. If the business takes off and people sign up for it, i'll get the whole Excel file transformed into a database/website. But, for now, we're using a shortcut where just the user inputs and final outputs are on a website while the rest of the file (hidden to the user) is still based on an excel file up in the cloud.
Page 2 of 2
Popular
Back to top
Follow TigerDroppings for LSU Football News