Register a SA Forums Account here!
JOINING THE SA FORUMS WILL REMOVE THIS BIG AD, THE ANNOYING UNDERLINED ADS, AND STUPID INTERSTITIAL ADS!!!

You can: log in, read the tech support FAQ, or request your lost password. This dumb message (and those ads) will appear on every screen until you register! Get rid of this crap by registering your own SA Forums Account and joining roughly 150,000 Goons, for the one-time price of $9.95! We charge money because it costs us money per month for bills, and since we don't believe in showing ads to our users, we try to make the money back through forum registrations.
 
  • Post
  • Reply
esquilax
Jan 3, 2003

do it posted:

I have no real knowledge of Excel, but managed to hack this together:

=IF((G9-J9+K9)<>0,"",(G9-J9+K9))

on the basis that the IF function works like this: =IF(test, "action if test is true", "action if test is false")

It's supposed to check to see if (G9-J9+K9) is equal to 0, and if so, display nothing (not just 0 - important workaround for Google Forms) in the cell. If (G9-J9+K9) is not equal to zero, it's supposed to evaluate (G9-J9+K9). Can anyone see what's going wrong here?

Your formula as written will only output 0 or nothing. It outputs nothing if it's not equal to zero ((g9-j9+k9<>0 is true), and if it is zero ((g9-j9+k9)<>0 is false) then it will output zero. You want to switch <> with =, so it will output nothing only when it's zero.

Adbot
ADBOT LOVES YOU

esquilax
Jan 3, 2003

mistermojo posted:

Ok, so I use this function to count across a row:

=(COUNTIF(A22:AB22, "E") + COUNTIF(A22:AB22, "S") + COUNTIF(A22:AB22, "U"))

what I want to do is use that kind of function to count across each row of x number of columns and sum it up. (so instead of A22:AB22 it would be A22:AB22, A23:AB23 etc)

How would I do that?

Do you know array formulas? Type in:
=sum(if(A22:AB24="E", 1, 0))
then hit ctrl+shift+enter, and it will count for rows 22 through 24.

esquilax
Jan 3, 2003

The function adds 1 every time each cell is "E", so it's just another way of counting. When the cell is not "E", it adds 0.

And as it turns out you don't need my formula. I thought countif only worked on a single row or column but I was wrong. Your way is simpler.

esquilax
Jan 3, 2003

unixbeard posted:

i manually enter a bunch of date, number values across 2 colums, e.g.

3/1/2010 | 3
2/1/2010 | 7
1/1/2010 | 1

then on the 4th it becomes

4/1/2010 | 2
3/1/2010 | 3
2/1/2010 | 7
1/1/2010 | 1

in another cell i have say the sum of the top 3 rows in the value column. When its the next day and i insert a new date, value observation, i cut the existing data and paste it one row down, then insert the new data above it. As expected Excel tracks all the cell refs, so what was SUM(B5:B7) becomes SUM(B6:B8) and the new value gets ignored.

How do i either make excel always use the values in B5:B7 even if data gets moved around, or is there some other way I could do it? I guess I could always write a macro that looks for the first number in column B then returns the range from that number + 3, but it seems like this is something excel might already deal with.

=sum(indirect("B5:B7")) treats "B5:B7" as text, so it will always use that range and keep it from updating.

esquilax
Jan 3, 2003

bhaltair posted:

I'm creating a template using Excel for data input into a database. The database doesn't allow blank cells in the upload, it requires an "NA" in the cell to represent null. I have included some calculation checks in the Excel template to ensure that users are transcribing the right data. To make it the most efficient and productive template I want to make all cells in the template default to "NA" (so analysts don't have to manually fill these in every time) but when I do that our calculation checks on the side don't work since they view it as a text string.

So here's my question: Is there anyway I can assign the numerical value 0 to the text string "NA"? Or is there anyway I can designate the formulas on the side, that do the calculation checks, to disregard any cell that has the text string "NA"?

I think either of those will enable me to do what I want. If you have any other ideas I'm open to them as well -- thanks!

The easiest way is to basically replace all cell references with if statements that return 0 if the cell reads "NA". For example, if cells B2 and C2 can be either numeric or "NA", replace:
=B2-C2

with

=if(B2="NA", 0, B2)-if(C2="NA", 0, C2)

esquilax
Jan 3, 2003

Acethomas posted:

I saw you said not 0 to 80 and 5 on 81-100 and thats what I need, I'm sorry I'm really bad at explaining this stuff, which is probably why I have such a problem figuring out where to look in help and online to learn this stuff.

Assume your goal is 100, and you sell 110. Under a flat scale the commission would be 10% of the 110, totaling 11 in commissions. Under a graduated scale, you would get 5% of (100-80) plus 10% of (110-100), totaling 2 in commissions. Are you looking for a flat scale or a graduated scale?

His formula is the one you want to use for a flat scale.

esquilax
Jan 3, 2003

factorialite posted:


Try changing your formula to:

=vlookup(sheetname($a$1,true),'Master'!$a$1:$b$33,2, false)

When you don't specify a last argument for a vlookup, it defaults to "True". This makes it look for an 'approximate' value, which gives you weird results if your list isn't in alphabetical order. Setting it as 'False' makes it look for an exact match, and you don't have to reorder anything.

esquilax
Jan 3, 2003

ZerodotJander posted:

Looks like you should be able to get something going using the INDIRECT function and concatenating pieces of your reference.

INDIRECT() can only reference other workbooks if the workbook is open.

The best way I can think of is to to create a text string that has your needed formula using concatenation and/or "&", then using VBA to input that string as the formula in a separate box.

esquilax
Jan 3, 2003

Ivan Drago posted:

Very simple question (I think):

What I want to do is find a value in an array and return a different value a certain number of columns over AND a certain number of rows down at the same time, like a combination of VLOOKUP and HLOOKUP. Is this possible?

As a quick means of reference, here's a simple table. How can I write a formula that finds "Jim" and returns his salary from this set of data? If there some sort of nested lookup function that would make this possible?
code:
     A       B       C
1    Jim     
2            Age     27
3            ID      2
4            Salary  $38,000
5    
6    Tom
7            Age     32
8            ID      8
9            Salary  $37,500
E: If I were to use =OFFSET(A1,3,2,1,1) this would work, but for the purposes of this file it would be easier if I could reference the lookup value by the text that inside ("Jim") rather than the cell itself (A1). I'm sure there's a way to nest an offset inside a lookup or a match or something along those lines but my brain isn't working today.

Try something like "=index(C:C, match("Jim", A:A, 0)+3)"

Index() can also change columns if you need it.

esquilax
Jan 3, 2003

I don't know much about pivot charts, but I do know that pivot tables don't let you compare across value types stored in different columns. Excel will basically never combine or compare 'apples' and 'pears' in a pivot table.

Your choices are either to not use a pivot chart at all (my recommendation), or reorganize your data.

This is one way to organize your data that will make the pivots work correctly, but seems cumbersome:
code:
Month     Fruit    Number
March     apple     0
March     apple     1
March     pear      1 
March     peach     1

esquilax
Jan 3, 2003

TraderStav posted:



If you're using VBA, the easiest way would be to just use a Do/While loop on the cells in Column U (Column 21) until you find a cell that isn't blank.

e.g.
code:
FirstValueRow = 2  'so it skips the heading
Do While Cells(FirstValueRow, 21).Value = ""
FirstValueRow = FirstValueRow + 1
Loop

esquilax
Jan 3, 2003

TraderStav posted:

To follow up on this, after it finds the first cell with data in it, how can I assign a variable to that cell location? I just found another line of code that needs to know exactly where that cell is, versus what row.

In the "Cells()" function, the syntax is cells(row, column). In my example, the column was 21 (U is the 21st letter).

You will need to create a variable (call it "FirstValueColumn" or something, it doesn't really matter), and make it equal to whatever column you're trying to look in. If you're only looking in column U, FirstValueColumn=21.

You can then use Cells(FirstValueRow, FirstValueColumn) to point to the exact cell you want.

Does that help?

esquilax
Jan 3, 2003

TraderStav posted:

So then Variable1 = cells(FirstvalueRow, FirstValueColumn) would make a variable with that location?

I don't think so, but you can basically use 'cells(FirstvalueRow, FirstValueColumn)' for most purposes that you would want to use a variable for.

What do you want to use it for?

esquilax
Jan 3, 2003

TraderStav posted:

Currently the code is:

nextMonthForwardPrice = Range("U3")

It needs to be

nextMonthForwardPrice = Range("U-whateverthefirstnonblankcellis")*

* with the blanks being formulas inputting "" as the value

Is nextMonthForwardPrice supposed to be a range (e.g. U1:U10), or just the value of whatever that cell is (e.g. U10)? If it's the latter, you can just use:

nextMonthForwardPrice = Cells(FirstvalueRow, FirstValueColumn).Value

If it's the former and you want it to be a range of values beginning at U1 and ending at that cell, you can use:
Dim nextMonthForwardPrice as Range
Set nextMonthForwardPrice = Range("U1", Cells(FirstvalueRow, FirstValueColumn))


Usually when you see code like "Range("$A$1")" it means someone used the "Record Macro" deal, which returns very ugly code.

esquilax fucked around with this message at 21:07 on May 26, 2011

esquilax
Jan 3, 2003

DankTamagachi posted:

I can probably write some VBA to rearrange the data to include separate rows for each item. If I manage that, how would I ensure the data looks like the mock-up above?

Or, you could just make a few extra columns with formulas and base a pivot off that.

For example, make E2 be "=if(A2="", E1, A2)" and copy it down to the bottom of the data. Then run a pivot off of columns C:E. You can hide the blank rows inside the pivot.

esquilax
Jan 3, 2003

Try checking the source data of your graph and see if you're referencing a bunch of blank cells. Having a bunch of blank categories after "Goal" and "Net" might make your graph look like that, because it would read the blank cells as columns of height zero.

esquilax
Jan 3, 2003

It's easy to make one chart with all of them on it, but I believe creating separate charts requires making each chart individually.

Use a pivot table on all of your data to rearrange your data like this:
code:
Hour	Allentown	Andrews
0	415
1	401		
...
6	446		236	
Then you just paste as values somewhere else, highlight the entire thing and create a connected scatter plot.


To create separate ones for each plant, just highlight the hour column and the first plant and create your chart for Allentown. It will be easiest if you create it as an object in your current sheet instead of a new sheet. Copy and paste that chart, then select the duplicate. The current data ranges should be highlighted, so all you need to do is click and drag the data range to Andrews.

With copying, pasting and moving data ranges you're looking at like 10-15 seconds per plant type for individual charts.

esquilax
Jan 3, 2003

HClChicken posted:

I'm confused with how I should program this vlookup formula.

Here's the gist:

I have 4 arrays for each age/ gender group. 5 age groups, for a total of 10 arrays. They are all labeled, GXXZ, where XX is the age group variable (30, 40, 50, 60, 61), and Z is the variable for each array (A, R, P, S), and G is gender notation (M/F) So I can have variables such as M30R, or F60P. This comes out to 40 total arrays, with up to 20 rows (all have two columns).

I use the formula =vlookup(target value,array,2,True) to go from the value to the point system. Target value are my A, R, P, S variables.

Problem is I have 50 people, who get older, and get taken off list/ added to the list. So I wanted to add a second function to change the array value (M30R) so it updates when I change it from 30 years old to 31 years old.

I created 8 new arrays that consist of the other 40 arrays. 4 based off female gender, 4 off male gender each subparts of A, R, P, S. The arrays look like this

30 M30A
40 M40A
50 M50A
60 M60A
61 M61A

That would be array "MA"

I then created a new lookup that is written like this =vlookup(age,MA,2,True). This works in the way that it spits out the proper MXXA array based off age.

I try to nest it such as =vlookup(A_target_value,vlookup(age,MA,2,True),2,True). So that it calculates the appropriate array based off age and then spits out appropriate point value based off target value.

This provides error n#a. Which doesn't make sense because the individual arrays (to calculate MXXA, and point value work. I've also tried to direct the =vlookup(A_target_value,array,2,True) formula to the result of a cell that only consists of =vlookup(age,MA,2,True), simply replacing "array" with b2. But it gives same error. Yet the direct link to the MXXA array works.

I've also considered using VBA to create if, then statements that replace the XX part of the vlookup formula based off age, and the Z part based off gender but I'd have to figure this out (as I haven't done VBA is a long time).

I probably should have brought this home from work, but I forgot to save it to my email. I hope this doesn't sound all :psyduck:
I'm having a hard time parsing your post, but it looks like you're trying to use a value that you either pointed to or did a vlookup to as a named range. If that's the case, you need to use the indirect() function to change it from a text string to a "named range" string. As in,

=vlookup(A_target_value,indirect(vlookup(age,MA,2,True)),2,True)

Is that what you meant?

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)

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.

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

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

esquilax
Jan 3, 2003

Heavy_D posted:

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.

I read his post as him having his values all in one row instead of two columns.

esquilax
Jan 3, 2003

Old James posted:

A coworker asked me to help her fix a spreadsheet she updates which was created by her predecessor. Looking at it I saw some strange syntax in the formulas that I do not understand and Google was not very helpful.

code:
=SUMPRODUCT(--('Raw Data'!$AU$2:$AU$4820=0),--('Raw Data'!$DL$2:$DL$4820<=4))
From the context of where the formula is used it is supposed to function like a COUNTIFS() but I've never seen the "--" before. Could anyone help explain to me what that does?

The stuff inside each parentheses evaluates to TRUE or FALSE. When you put a -- in front of it it multiplies by -1 twice, which turns the TRUE and FALSE into 1 and 0 respectively, which is the necessary syntax for the sumproduct() function. There are other ways to do the same thing but -- works.

esquilax
Jan 3, 2003

GregNorc posted:

Is there something to SUMIF() that I'm missing?

I have some experiment data and I'm trying to do some really basic calculations in Excel instead of using R.

For example, getting the number of males in the sample.

So I wrote:

=SUMIF(C2:C26,"M")

Which allegedly gives me zero results. The data is such that in column c, males have the letter "M" and females have "F" (without quotes)... so I'm blanking on why this would fail... I thought maybe there was some invisible characters in there, but creating a test case where I know there's just M and F still gives a zero result...

Use COUNTIF(). SUMIF() tries to add all the values that equal M, and "M"+"M" = 0

esquilax
Jan 3, 2003

So my office recently upgraded from MS Office 2003 to Office 2010.

Most people there typically create tables in Excel and paste as a picture (enhanced metafile) into PowerPoint. When you do that using Office 2010 it copies the default gridlines and the red comment triangle and shows them when you paste the table. To make it look proper I need to remove gridlines and make sure the comments aren't being shown before I copy, which is annoying.

Is there any way copy and paste it as it show up if printed (i.e. no default gridlines, no comment or error triangles)?

esquilax
Jan 3, 2003

celestial teapot posted:

To remove gridlines, fill the cell color as white and clear any borders that may have been applied to the cell styles.

To remove error flags, :google: holds the answer to this question.

I'm not familiar with power point but I am sure there is a better way to import your data into it.

Well obviously. Those were the solutions that I posted to my own question. I just don't want to take an extra 15 seconds to hide grids and flags, copy and paste, and then spend another 15 seconds turning them back on.

I was asking if there is a better way to import.

esquilax
Jan 3, 2003

It used to be the case that pasting excel tables in PPT would be awful and mess everything up. But apparently it solves my problems if I paste a table using "Keep Source Formatting" (Alt-h-v-k) which shows up under Paste Options and not the Paste Special menu. New PPT appears to be a lot better at dealing with Excel->PPT table pasting, so that's what I'm going with.

Thanks.

esquilax
Jan 3, 2003

melon cat posted:

Is there an easy way to make Excel calculate and display ratios?

For example if A1 = 8, and A2 = 24, I want A3 to show 1:3. But I can't find any easy way to do this (or do it at all, for that matter...).

Suggestions?

You can calculate the ratio manually, and manipulate the text so that it shows up in that format. Essentially:

=A1/GCD(A1, A2) & ":" & A2/GCD(A1, A2)

esquilax
Jan 3, 2003

Xovaan posted:

There's many duplicates in the list I'm pasting data to with the unique identifier being the year next to their name. I'm trying to make sure that 2009 / Bob / #### transfers #### to the correct "2009 / Bob / (blank)" and nothing else.

I ended up concatenating the two columns together for a simple vlookup but I feel like that's cheating and there is a way to copy data with a two-cell criteria. :raise:

Concatenating is the easiest method. The other method is to use an array formula like ={INDEX(Data!$C$1:$C$100, MATCH((Data!$A$1:$A$100=A1)*(Data!$B$1:$B$100=B2), 0))}

It's not recommended unless you're familiar with array formulas.

esquilax
Jan 3, 2003

melon cat posted:

I'm at my wit's end, here. I'm trying to create a "staircase" style chart in Excel 2010. Like this:



But I'm having a lot of trouble figuring out how. I've checked other resources using Google, but a lot of the alternate approaches I've seem far more complicated than I think they need to be. What's the best way to go about creating this type of chart?

The easiest way is to double your data set, and add in data points just before where you want it to "jump"

If your data looks like this:
code:
1    11
2    14
3    16
4    17
Use your Excel-fu to change it to something like this:
code:
1       11
1.9999  11
2       14
2.9999  14
3       16
3.9999  16
4       17
4.9999  17
Then make it a scatter chart and add a line.

esquilax
Jan 3, 2003

In case you haven't found the answer yet, In the x-axis format menu, select "Number", and give it a custom format code of "yyyy".

esquilax
Jan 3, 2003

Old James posted:

What about a formula?

=if(and(iserror(search("Console",B2)),iserror(search("table",C2))),"","Console Table")

Your formula is slightly off, it should be an OR() rather than an AND() since you want the IF statement to return false only on a (False, False) result.

esquilax
Jan 3, 2003

Shmoogy posted:

This appears to work - after switching and to or - but I don't think it will do exactly what I need, as I need this to work for Console Tables, End Tables, Side Tables, etc.

If I change the term, and re-drag the formula, it overwrites the Console ones with a blank entry - is there a way to do this with a list/array - or a way to incorporate this into a script to run the formula through the column, and lock the rows that were modified with the anticipated final result?

Do you know VBA? Try something like this. Note the function InStr() is case sensitive.

code:
Sub TableSearch()

EndRow = 1000
Dim TableTypes(3) As String

TableTypes(1) = " CONSOLE "
TableTypes(2) = " END "
TableTypes(3) = " SIDE "

For i = 3 To EndRow
    If Cells(i, 3).Value = "table" Then

        For j = 1 To 3
            If InStr(1, Cells(i, 2).Value, TableTypes(j)) <> 0 Then
                Cells(i, 4).Value = TableTypes(j)
            End If
        Next j

    End If
Next i

End Sub

esquilax
Jan 3, 2003

melon cat posted:

I'm having some trouble getting a stacked bar chart to behave the way I want it to. This is what I keep getting, but it isn't what I want:

I want:
The Dates on the X-Axis
$ dollar amounts on the Y-Axis
The series labels to be shown in $ dollars
... and the stacked bars to vary in height according to their total dollar amount.

How the heck do I get this chart to behave the way I want it to? :confused:

There are a few types of stacked bar charts. You picked one that always adds to 100%.

Right click -> Change Chart Type -> Stacked Column

esquilax
Jan 3, 2003

melon cat posted:

A question about creating charts using number data.

I'm using Excel 2010. Every time I create a chart (Select Data > F11 to create pie chart in new worksheet) Excel keeps creating a pie chart with a lot of white space. But I don't want that white space, and it won't let me resize the area outside of the bounding box.



Why is it doing this, and how can I eliminate that white space?

When you create a chart in a separate sheet, the size of the sheet is dependent on the page size. So it's 8.5x11 by default. You can change the size of the whitespace by changing the page dimensions.

Not sure how to stop that though, or if it's possible to create a square page. Maybe adjusting the margins would work.

edit: Yeah, adjusting the margins works. Just set the right margin to 3.3" or whatever and it becomes a square

esquilax fucked around with this message at 22:06 on Mar 5, 2014

esquilax
Jan 3, 2003

melon cat posted:

Unfortunately that solution isn't working for me. It looks exactly the same. :(

I tried to centre-align the chart so it doesn't look too terrible, but the heck do you Centre-align charts, any way? All of my alignment tools are grayed out.

Not sure why it's not working for you, I just did it again in excel 2010. You're going Page Layout->Margins->Custom Margins->Right=3.3" correct?

If you're really concerned just embed it into another worksheet instead of making it a sheet by itself, it should be more workable.

esquilax
Jan 3, 2003

Loving Africa Chaps posted:

I've got what im sure is an easy problem i can't sort out by myself.

I have a spreadsheet where i'd like a script to run down part of a coloumn and increment each cell it turn by one untill another cell on the same row becomes 3

code:
Private Sub CommandButton1_Click()
For Each cl In ActiveSheet.Range("B7:B27")
cl.Value = "1"
i = ActiveCell.Offset(0, 10).Select
Do
cl.Value = cl.Value + 1
Loop While i < 3

Next cl

    
End Sub
is what i've got so far but it and variations i've tried either end after the first loop or just go in indefinately incrementing the first cell. I'm sure im doing something retarded, please help

The main issue is that your Do loop doesn't increment i at all.
The code sets i = ActiveCell.Offset(0, 10).Select, then i doesn't ever change while you're in the loop. Try setting i to the value in the target cell somewhere inside the Do loop.

I doubt you'll be able to make the method with ActiveCell.Offset(0, 10).Select work. Try something like i = cl.Offset(0, 10).Value

esquilax
Jan 3, 2003

Ronald McReagan posted:

I couldn't figure out how to make the Solver tool generate rows, however it will help me solve the problem that results from generating the rows, so thanks for pointing it out!

I did find a pretty simple VBA solution to my original problem:

code:
Dim i1 As Integer, i2 As Integer, i3 As Integer, i4 As Integer, k As Integer

k = 1

For i1 = 0 To 2
For i2 = 0 To 2
For i3 = 0 To 2
For i4 = 0 To 2
    Cells(k, 1).Value = i1
    Cells(k, 2).Value = i2
    Cells(k, 3).Value = i3
    Cells(k, 4).Value = i4
    k = k + 1
Next i4
Next i3
Next i2
Next i1
I would then do a find/replace to change 0,1,2 to A,D,S.

Follow-up question: if someone else has more or fewer evaluation results (just Awesome/lovely, or Awesome/Decent/lovely/Really lovely) and wants to do it across more or fewer years, are they going to have to futz around with the code themselves, or is there a way to pop up a dialogue box that asks "How many different evaluation results?" and "How many years?" and the user can just put the two numbers in and get what they want?

You can do that by using a mathematical algorithm instead of a coding one.

For example, you have n+1 number of grades (A,D,S) and m number of years.
code:
m = 4
n = 2

For i = 0 To (n+1) ^ m - 1
For j = 1 To m

Cells(i + 1, j).Value = Int((i Mod (3 ^ j)) / (3 ^ (j - 1)))

Next j
Next i

esquilax fucked around with this message at 20:32 on Apr 7, 2014

Adbot
ADBOT LOVES YOU

esquilax
Jan 3, 2003

Oops, yep.

  • 1
  • 2
  • 3
  • 4
  • 5
  • Post
  • Reply