Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »

totalnewbie: Nov 13, 2005; I was born and raised in China, lived in Japan, and now hold a US passport.

I am wrong in every way, all the damn time.

Ask me about my tattoos.

Yeah I can do that but it doesn't seem to remove the unformatted cells from being taken into consideration in the gradient formatting. So if you changed the first one to 1/1/2010, all the other ones will turn (almost) a single color.

# ? Sep 7, 2011 20:29

Adbot: ADBOT LOVES YOU

# ? Apr 27, 2024 21:54

Sub Par: Jul 18, 2001; Dinosaur Gum

Sorry, I misunderstood your problem. You want to use an array formula. In your "highlight cells based on their values" rule, for the min and max select "formula" and put this in for the minimum:

code:

=MIN(IF($B:$B="N",$A:$A))

And this in for the maximum:

code:

=MIN(IF($B:$B="N",$A:$A))

after you type/paste them in, press alt+enter. That should do the trick. Example:

# ? Sep 7, 2011 21:14

totalnewbie: Nov 13, 2005; I was born and raised in China, lived in Japan, and now hold a US passport.

I am wrong in every way, all the damn time.

Ask me about my tattoos.

That works well, though just to add that in Excel 2007 at least (and on this old laptop), I had to cut the range from $B:$B to $B$1:$B$whatever else it would slow down the spreadsheet a lot.

Thank a lot!

# ? Sep 8, 2011 15:13

AJzer: Nov 28, 2004; Tally Ho!

I have an optimization question about vlookup and hlookup functions. Is there any reason to be using these for exact match instead of direct cell references on say, 100x5000 (x3) sized arrays?

I feel like if excel does what I think it does, it would be searching these arrays for a match every time there is a change in the workbook. Compared to direct reference, this has to be slower, no?

# ? Sep 8, 2011 18:46

coyo7e: Aug 23, 2007; by zen death robot

Any recommends for good Excel VBA reference books? I'm doing a project at work and can get reimbursed for them easily, but I'm currently working out of a Visual Basic 6 "teach yourself in 21 days!" book, and a VBA for MS Access book, which my boss thinks is usable, but it doesn't really have any of the poo poo I want and need.. :argh:

I used to be good at this poo poo but it's been like 6 years and my memory atrophied in the excessive drinking to anesthetize my tier 1 support woes..

# ? Sep 14, 2011 18:33

Sub Par: Jul 18, 2001; Dinosaur Gum

What kind of stuff are you needing to do? UI type "button click does these events" stuff, data manipulation, other stuff? Honestly I haven't read any books that are super helpful and generally work until I get stuck at which point I google, which usually works just fine.

In a pinch I've walked down to the Barnes and Noble and peeked in a few books, both the Oreilly "Programming Excel with VBA and .NET" title and the VBA for dummies one ( :lol:

but it is decent) were helpful. But I'd suggest not buying a book unless you really learn best that way.

# ? Sep 15, 2011 00:47

coyo7e: Aug 23, 2007; by zen death robot

Sub Par posted:

What kind of stuff are you needing to do? UI type "button click does these events" stuff, data manipulation, other stuff? Honestly I haven't read any books that are super helpful and generally work until I get stuck at which point I google, which usually works just fine.

In a pinch I've walked down to the Barnes and Noble and peeked in a few books, both the Oreilly "Programming Excel with VBA and .NET" title and the VBA for dummies one ( but it is decent) were helpful. But I'd suggest not buying a book unless you really learn best that way.

I'd like to think that I'm above a 'Dummies' book after spending 3 years at an engineering school working toward a software engineering degree before changing my mind.. Although I'm rusty enough I maybe should be reading one!

I ended up getting "Excel 2010: Power Programming with VBA", which seemed to be the closest thing to what I was looking for and that was on the shelf at B&N. I really like it so far, it's easy to follow, has some decent examples, but isn't a just a literal reference manual of function names and parameters etc, since I'm not back up to that level of fluency, yet.

I haven't quite gotten quite there yet, but there is one thing I'm trying to do with a UserForm, and thought someone here might be able to tell me if I'm thinking of it correctly or not: I'm making a vacation/sick time calculator which allows peoelp to enter in employees with a UserForm, and I want to have a couple radio buttons which allow someone to choose 'Hourly' or 'FTE' employees.. This changes how vacation hours will be calculated (hourly get one static multiplier, FTE gets a scaled accrual rate based on length of employment), but right now I'm trying to make the form either show a TextBox to enter "hours worked" on the Form if a person is hourly, or to show a (maybe 2) TextBox for entering their FTE "hours", which would be calculated on a 0.0 to 1.0 scale, and then multiplied to the hours they would have worked that particular month.

Any feedback or ideas? I'm thinking the radio buttons will toggle a boolean since that'd be the easiest way to make a formula automatically calculate stuff, but I'm not sure if I how to hide one box and show another, yet, but It'd be simplest to have normal hourly and FTE employees' "hours" in one field, and then have another field which would either be "HWS" or a numerical value, which would be easy to make an if() statement for so the 'boolean column' may not be necessary at all - Hourly employees WOULD need to have hours entered, but FTE maybe should auto-calculate based off the multiplier.. I may be barking up the wrong tree, though..?

This is reminding me why I hated Visual Basic back in school, though..

coyo7e fucked around with this message at 19:29 on Sep 19, 2011

# ? Sep 19, 2011 19:06

gwar3k1: Jan 10, 2005; Someday soon

Use a group panel to show/hide multiple elements with minimal code. Have two groups, one over lapping the other and your radio buttons outside of them both (underneath?).

You can add code to the radio change event in the VBE (double click the radio button on the designer, then top right of the code window is a drop down menu with the different events for the element in the menu to the left). If true then group1.visible = true else .visible = false and the opposite for the other radio button.

You could use the scroll button tool for a 0 - 1 scale.

# ? Sep 20, 2011 23:16

coyo7e: Aug 23, 2007; by zen death robot

Thanks for the tips! I think I've got it figured out with an alternate method (set each radio button to input a short text string that the finance people wanted in there, anyway, then using an if() on those, to calculate the hours, which will all go into one column but be calculated differently depending on HWB vs FTE) but I'll definitely play with that stuff some more!

# ? Sep 22, 2011 01:24

Paino: Apr 21, 2007; by T. Finninho

I'm very bad at excel and in dire need of a formula that will make my life easier.

I have three worksheets:

On 1 I have a client's P.O. with product codes (col A) and quantities (col B)
On 2 I have my company's product codes (col A), corresponding stock (col B), job no. (col C) and P.O. no. (col D) associated
On 3 I have, again, product codes (col A) with correct packaging (for ex:8500 per box) for each (col B)

This is what I want to do:

- Compare Client!A with Company!A and automatically delete all the horizontal strings that don't contain the same product codes. This seems easy with cond. formatting but is not because Company!A contains duplicate codes of its own. So basically duplicates in Company!A can only stay if the code is also found in Client!A, the rest needs to be deleted.

- Now I need to compare Client!B (p.o. qty) with Company!B (company's stock) product by product, and if the client's value is inferior (meaning there's stock enough), do a CEILING on the Packaging value of the corresponding product code.

- The end result is basically having a Table with all the client's products that are covered by the current company stock, in qtys that are also multiples of the associated Packaging (the CEILING is because the final quantity has to be a multiple of Packaging but also obviously > than the qty the client ordered)

I have used a couple of basic formulas that do part of the work but I still feel this stupid crap is taking too long. I suppose I'm not too good at linking them or heh maybe I'm doing something wrong. Any kind of help is appreciated.

# ? Sep 24, 2011 12:08

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Kind of an odd question, when saving a worksheet I got this warning today:
"Privacy warning: This document contains macros, ActiveX control, XML expansion pack information, or Web components. These may include personal information that cannot be removed by the "Remove personal information from file properties on save" option on the Security tab of the Options dialog box on the Tools menu."

What's weird is that I'm the only one who edits this sheet, and all of my edits are entirely textual (it's actually just a central place where we keep intranet URLs and stuff like that). Is there any way to get a list of the aforementioned xml/activex/macros etc. that would be causing the error? It's Excel 2003 if that makes a difference.

# ? Sep 26, 2011 21:42

coyo7e: Aug 23, 2007; by zen death robot

Anybody got a link to some decent VBA stuff for detecting duplicate entries? I'm working on adding some error-checking to pop up a warning and prevent entering two employees with the same employee ID number.

I'm using a table so sorting first should make it a bit quicker.

# ? Sep 26, 2011 21:55

ZerodotJander: Dec 29, 2004; Chinaman, explain!

How big is your table? If it's not huge you can just set a conditional format to highlight duplicates.

# ? Sep 26, 2011 23:12

Dr.Magnificent: Dec 24, 2007; Comes with hands on care.; Fun Shoe

For checking on entry, if you don't have a ludicrous number of entries, I just use Application.WorksheetFunction.countif and have an if statement check if it is greater than 0, add to set or return a message box as needed.

# ? Sep 27, 2011 01:36

qntm: Jun 17, 2009

I put all of my bank statement information in a single long table in a spreadsheet. One column for date, one column for "Who", one for "amount", one for "running total", one for "What/why" etc. I know how to use filters to just show entries for e.g. "wages", or just show money going to/from my Dad. But is there any way to create a new spreadsheet which is a filtered version of the old one, so that when I update the old one the new one gets updated too? Then I can have semi-permanent graphs based on the filtered table.

# ? Oct 9, 2011 13:44

gwar3k1: Jan 10, 2005; Someday soon

Setup some pivot tables, or a single pivot table and make yourself a dashboard. I've recently got into them and they've made my life so much easier.

# ? Oct 9, 2011 18:56

coyo7e: Aug 23, 2007; by zen death robot

Okay I'm trying to wrap my head around some date-oriented math, and am hoping that someone might be able to point me to a more efficient way of dealing with this.

Background:
People start on day X (type: date variable). In a perfect world, this would always be the 1st, for type A (type: text) employees, and on the 16th, for type B (type: text) employees.

After 90 days AND on the next pay period, they become eligible for benefits. (yes, this means if you are type A and start on the 2nd, you get hosed and have to wait 120 or so days, until the following first day of the month comes around. That's what HR set up and told supervisors, but people still get hired on random days, so whatevs.)

So I'm trying to figure out the most efficient way of providing an eligibility column. I have a column Displaying A or B, and another with a "date" value, and I want to figure out a somewhat elegant way to calculate the first date/pay-period they would be eligible for benefits. Somehow subtract/remove only the "days value" from that date (basically leaving only yyyy-mm) and add 3 months, then force it to 1st/16th based off A/B employee type? Run some sort of weird date-based modulo function I'm not aware of?

I'm sure there's a slick way to do this but I feel like I'm thinking too hard at it and missing something obvious..

qntm posted:

I put all of my bank statement information in a single long table in a spreadsheet. One column for date, one column for "Who", one for "amount", one for "running total", one for "What/why" etc. I know how to use filters to just show entries for e.g. "wages", or just show money going to/from my Dad. But is there any way to create a new spreadsheet which is a filtered version of the old one, so that when I update the old one the new one gets updated too? Then I can have semi-permanent graphs based on the filtered table.

Yeah especially since you want to be able to show filtered versions of the information on demand and have it carry through when you update the main, a pivot table sounds like what you'd likely want.

coyo7e fucked around with this message at 19:43 on Oct 14, 2011

# ? Oct 14, 2011 19:41

esquilax: Jan 3, 2003

coyo7e posted:

Okay I'm trying to wrap my head around some date-oriented math, and am hoping that someone might be able to point me to a more efficient way of dealing with this.

Background:
People start on day X (type: date variable). In a perfect world, this would always be the 1st, for type A (type: text) employees, and on the 16th, for type B (type: text) employees.

After 90 days AND on the next pay period, they become eligible for benefits. (yes, this means if you are type A and start on the 2nd, you get hosed and have to wait 120 or so days, until the following first day of the month comes around. That's what HR set up and told supervisors, but people still get hired on random days, so whatevs.)

So I'm trying to figure out the most efficient way of providing an eligibility column. I have a column Displaying A or B, and another with a "date" value, and I want to figure out a somewhat elegant way to calculate the first date/pay-period they would be eligible for benefits. Somehow subtract/remove only the "days value" from that date (basically leaving only yyyy-mm) and add 3 months, then force it to 1st/16th based off A/B employee type? Run some sort of weird date-based modulo function I'm not aware of?

I'm sure there's a slick way to do this but I feel like I'm thinking too hard at it and missing something obvious..

Yeah especially since you want to be able to show filtered versions of the information on demand and have it carry through when you update the main, a pivot table sounds like what you'd likely want.

You're looking for the year(), month(), and day() functions if you want to do 'modulo' type math on dates.

The easiest way is probably using the eomonth(date, m) function, which returns the date the end of the month for m months past your start date. So if the date is 11/1/2011, =eomonth(date,3) is 2/29/2012. =eomonth(date, 3)+1 is 3/1/2012 (first pay date for A employees) and =eomonth(date, 3)+16 is the 15th of that month (for B employees)

# ? Oct 14, 2011 20:59

coyo7e: Aug 23, 2007; by zen death robot

Cool, there ARE modulo type functions, I couldn't ask for much more elegant of a solution than that, thanks!

I also found the dateadd() function, which seems to allow to add/subtract by anything you want, radical..! http://www.techonthenet.com/excel/formulas/dateadd.php

# ? Oct 14, 2011 21:21

coyo7e: Aug 23, 2007; by zen death robot

Okay, I think I got it, thanks a ton! :krad:

code:

=IF(F9="HWB", 
    IF(
       DAY(R2)<=16, EOMONTH(R2,2)+16, EOMONTH(R2,3)+16
       ), 
       IF(
          F9="FTE", 
          IF(
             DAY(R2)=1, EOMONTH(R2,2)+1, EOMONTH(R2,3)+1
            )
         )
  )

coyo7e fucked around with this message at 22:06 on Oct 14, 2011

# ? Oct 14, 2011 21:56

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

Here goes, hopefully words can explain what I mean here.

I've been tasked to streamline the reporting on a number of things to help make a couple of people in department's lives not poo poo.

The basic premise is we have a list of data, I pull this from a database, easy enough.

Said data is updated in the database reasonably regularly, so I will need to refresh the sheet every now and then (looking at weekly, could be daily). Again easy enough.

People A and B both will be editing this data, maybe some shared workbook issues but I'll work through\around them, even I have to shout only one at a time at them.

Company X will be sending up further updates to the data, (basically what they think it is) this needs to be cross checked against our data to make sure we're all on the same page. Again easy enough and I'll just highlight any discrepancies to people A & B as problems between data sets normally means poo poo went wrong or something needs to be done.

Reports need to be generated (pretty simple ones) on the fly and able to be sent on. Again easy enough.

My problem is how would you approach throwing all that together. The bit that's really troubling me is the data, the main master list will be drawn from the database at the start and then will just need updating, how would you handle that given the records that have been changed shouldn't be overwritten, I'll probably just highlight when there are discrepancies again and let them do it manually, but anything auto mated would be pretty nice.

Finally, since both could do with being able to access it all the same time, I'm thinking maybe their own workbook each and a sort of hub workbook in the middle to help prevent data corruption, been a while since I've worked with excel this doable easily enough?

Oh and yes, I know, there are far better things to do this on than excel. But it has to be excel because :smith:

# ? Oct 17, 2011 17:36

gwar3k1: Jan 10, 2005; Someday soon

Set up an interface using vba and forms which allows users to edit rows and commit them to the database rather than allowing them to change them directly on the sheet.

An alternative, or parallel solution, depending on just how large your data set is copy the data sheet to a hidden sheet then when the user changes a cell on their visible sheet, you have a flag on the hidden sheet to say changed. Update that row safe in the knowledge any uniquely identifiing data hasn't been modified.

Rather than using a second sheet, you could prevent changes by modifying the cell handling. On cell entry, create a string variable with the original content. On cell change, check to see if it's changed - if so and it shouldn't, use the variable to set it back or if it should be changed, set your flag to alert your code to update that record.

I'd say the interface method is the way to go though. How much data do they need to see in the first place? Can they query what they need rather than have it all on show?

# ? Oct 18, 2011 18:25

mcmagic: Jul 1, 2004; If you see this avatar while scrolling the succ zone, you have been visited by the mcmagic of shitty lib takes! Good luck and prosperity will come to you, but only if you reply "shut the fuck up mcmagic" to this post!

Does anyone know a macro that would search a huge spreadsheet, search for numbers with offsetting absolute values and seperate those items?

# ? Oct 19, 2011 16:24

ZerodotJander: Dec 29, 2004; Chinaman, explain!

I'm not really sure what you are asking.

# ? Oct 19, 2011 17:57

do it: Jan 3, 2006; don't tell me words don't matter!

I have two lists of companies, I'd like to find out which companies are in column A that aren't in column B. MATCH() seems like overkill? How should I be doing this?

# ? Oct 19, 2011 20:49

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Do a vlookup for each item from column A into column B, the ones that error out are the ones that are missing from column B.

# ? Oct 19, 2011 20:52

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

ZerodotJander posted:

Do a vlookup for each item from column A into column B, the ones that error out are the ones that are missing from column B.

Is there any resource that ranks functions based on how processor intensive they are? In this example a match, vlookup, or coutif would all get the job done but if you were doing this for large datasets which one would be the quickest to calculate?

# ? Oct 21, 2011 05:37

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

Add advanced filter to that list as well.

# ? Oct 25, 2011 17:44

FuzzyBuddha: Dec 7, 2003

I suspect the following request is fairly easy, but I'm kind of an Excel neophyte.

I have two spreadsheet files. In file A I have customer data. Headings include: Customer Name, Address, EID and Service Level. In file B I have EID data. This includes the headings EID, BTS and Service Level. File A has most of the important data in it, but does not include BTS.

What I'd like to do, is find some way to take the two files, compare the EID field in A to the EID in B. If it finds a match, I'd like A to fill in for that record the BTS and Service Level as found in file B (Service Level in A does not necessarily match the Service Level in B. Inconsistencies like this are something I'm trying to track down...)

Example - Say this is File A:

And this is File B:

What I'd like is for Billy Ray's record in File A to fill the BTS field with RED and the Service Level in EMS to Premium. Then fill June's record with GREEN in BTS and Deluxe in Service Level in EMS.

Everything about this screams this should be an easy, built in function for comparison in Excel, but I can't for the life of me figure out how...

Edit - Oh, and would it be easier, if both sets of data were just separate worksheets of the same file. The only reason they are different files now is because the two sets of data come from two different people...

FuzzyBuddha fucked around with this message at 01:29 on Oct 26, 2011

# ? Oct 26, 2011 01:26

esquilax: Jan 3, 2003

FuzzyBuddha posted:

I suspect the following request is fairly easy, but I'm kind of an Excel neophyte.

I have two spreadsheet files. In file A I have customer data. Headings include: Customer Name, Address, EID and Service Level. In file B I have EID data. This includes the headings EID, BTS and Service Level. File A has most of the important data in it, but does not include BTS.

What I'd like to do, is find some way to take the two files, compare the EID field in A to the EID in B. If it finds a match, I'd like A to fill in for that record the BTS and Service Level as found in file B (Service Level in A does not necessarily match the Service Level in B. Inconsistencies like this are something I'm trying to track down...)

Example - Say this is File A:

And this is File B:

What I'd like is for Billy Ray's record in File A to fill the BTS field with RED and the Service Level in EMS to Premium. Then fill June's record with GREEN in BTS and Deluxe in Service Level in EMS.

Everything about this screams this should be an easy, built in function for comparison in Excel, but I can't for the life of me figure out how...

Edit - Oh, and would it be easier, if both sets of data were just separate worksheets of the same file. The only reason they are different files now is because the two sets of data come from two different people...

You are looking for the vlookup() function.

Your formula in File A, cell D2 would be something like:
=vlookup(C2, [Range for file B], 2, FALSE)

That checks for the value of cell C2 (ffac1234) in the first column of that range of File B, then returns the value in the 2nd column for that entry (which would be RED). The 'FALSE' means it looks for an exact match. If it doesn't find a match, it returns an error.

It doesn't really matter if it's in a separate file or not.

# ? Oct 26, 2011 01:46

FuzzyBuddha: Dec 7, 2003

esquilax posted:

You are looking for the vlookup() function.

Your formula in File A, cell D2 would be something like:
=vlookup(C2, [Range for file B], 2, FALSE)

That checks for the value of cell C2 (ffac1234) in the first column of that range of File B, then returns the value in the 2nd column for that entry (which would be RED). The 'FALSE' means it looks for an exact match. If it doesn't find a match, it returns an error.

It doesn't really matter if it's in a separate file or not.

Perfect.

Thanks much for that. I'd seen vlookup mentioned earlier, but wasn't sure if it was what I needed.

Now an odd tack on question. When I copy that down the spreadsheet the formula auto-increments the cell references. Which is fine for the first part of the lookup code, but it's also incrementing the range from table 2. What that means is that it's narrowing the scope, so as I get further down the data, it's resulting in false negatives.

For example: For the first record, the formula is:
=VLOOKUP(I2, Sheet2!A2:F430, 2, FALSE)

But by the last record, the formula has changed to:
=VLOOKUP(I355, Sheet2!A355:F783, 2, FALSE)

Anyway to prevent that?

# ? Oct 26, 2011 02:52

esquilax: Jan 3, 2003

Putting a $ before your references keeps them from changing when you copy the formula.

So change Sheet2!A2:F430 to Sheet2!$A$2:$F$430

# ? Oct 26, 2011 03:18

No Safe Word: Feb 26, 2005

esquilax posted:

Putting a $ before your references keeps them from changing when you copy the formula.

So change Sheet2!A2:F430 to Sheet2!$A$2:$F$430

And note that you can do it on a column or row level if you want one to stay relative but the other to not

eg,

$A$1 copied anywhere stays as $A$1
A$1 copied from B2 to C2 becomes B$1
$A1 copied from B2 to B3 becomes $A2

# ? Oct 26, 2011 04:03

FuzzyBuddha: Dec 7, 2003

You guys rock. Thanks much. This is gonna make a number of things so much quicker.

# ? Oct 26, 2011 04:14

coyo7e: Aug 23, 2007; by zen death robot

esquilax posted:

Putting a $ before your references keeps them from changing when you copy the formula.

So change Sheet2!A2:F430 to Sheet2!$A$2:$F$430

Anybody who works with spreadsheets and doesn't know how to use $, go to google and start looking up "relative and absolute cell referencing". There are hundreds of videos on it, and after just a few minutes you'll increase the stability of your spreadsheets and the range of your abilities.

I come from a programming background so I take this stuff for granted but it's amazing how many people wrestle with this because they don't even know they can use $.

# ? Oct 26, 2011 04:36

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

coyo7e posted:

Anybody who works with spreadsheets and doesn't know how to use $, go to google and start looking up "relative and absolute cell referencing". There are hundreds of videos on it, and after just a few minutes you'll increase the stability of your spreadsheets and the range of your abilities.

I come from a programming background so I take this stuff for granted but it's amazing how many people wrestle with this because they don't even know they can use $.

Or that F4 toggles between the various relative and absolute references for the columns and rows.

# ? Oct 26, 2011 05:35

Elston Gunn: Apr 15, 2005

I have a bunch of values in a spreadsheet and I would like to add four columns between each value and then fill the empty spaces with equal amounts of the difference. Example:
5 10 becomes 5 6 7 8 9 10

I've tried using regression but the data isn't fit very well using linear or polynomial equations.

# ? Nov 4, 2011 21:12

esquilax: Jan 3, 2003

Elston Gunn posted:

I have a bunch of values in a spreadsheet and I would like to add four columns between each value and then fill the empty spaces with equal amounts of the difference. Example:
5 10 becomes 5 6 7 8 9 10

I've tried using regression but the data isn't fit very well using linear or polynomial equations.

There's a way to do it with formulas. It's extremely clunky but should get the job done for your purposes.

Set up your spreadsheet like this:

code:

Row 1: Numbers increasing from 0 to whatever, starting in column A
Row 2: Your values (i.e. 5 10 etc.), again starting in column A

For rows 4, 5, and 6, you just start the formulas in column A and copy them to the right for as many values as you need.
Row 4: =MOD(COLUMN()-1, 5)
Row 5: =FLOOR((COLUMN()-1)/5, 1)
Row 6: =IF(A4=0,HLOOKUP(A5,$A$1:$G$2,2,FALSE), HLOOKUP(A5+1,$A$1:$G$2,2,FALSE)*A4/5 + HLOOKUP(A5,$A$1:$G$2,2,FALSE)* (5-A4)/5)

Your result ends up in row 6. What that formula does is, every 5 columns, it takes the corresponding number from your values. Between those rows, it does a weighted average the two values based on which column you're in. You will need to edit the hlookup() reference in row 6 for your particular data set (mine only goes up to column G)

You can then paste row 6 as values somewhere else to do your work.

esquilax fucked around with this message at 21:53 on Nov 4, 2011

# ? Nov 4, 2011 21:50

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Edit: never mind.

# ? Nov 4, 2011 21:50

Adbot: ADBOT LOVES YOU

# ? Apr 27, 2024 21:54

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

Elston Gunn posted:

I have a bunch of values in a spreadsheet and I would like to add four columns between each value and then fill the empty spaces with equal amounts of the difference. Example:
5 10 becomes 5 6 7 8 9 10

I've tried using regression but the data isn't fit very well using linear or polynomial equations.

I've seen esquilax's post but isn't it just easier to do

A1: 5
B1: A1 * 0.8 + F1 * 0.2
C1: A1 * 0.6 + F1 * 0.4
D1: A1 * 0.4 + F1 * 0.6
E1: A1 * 0.2 + F1 * 0.8
F1: 10

Then just autofill the formula down the columns B to E.

# ? Nov 4, 2011 23:27

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »