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 »

mistermojo: Jul 3, 2004

another (probably) stupid question: How do I get the print titles (same row on each page) to display in Normal view?

# ? Apr 14, 2010 19:16

Adbot: ADBOT LOVES YOU

# ? Apr 24, 2024 09:53

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Freeze pane.

# ? Apr 14, 2010 19:21

bhaltair: Jan 7, 2008

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!

# ? Apr 15, 2010 19:24

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)

# ? Apr 15, 2010 19:33

Jagershot: Jun 7, 2004; RIP Mike V, 1989-2007. Have fun mounting Bear Bryant up in heaven.

I need an Excel macro that will search for values in a cell range (D6:AZ600, for example) on each page of workbook and delete them if they are equal to zero. I suck at VBA, though. Does anyone know how to do this?

Click here for the full 670x506 image.

This is a screenshot. I highlighted the zero values in yellow. There are about 20 tabs in this workbook that I need to delete the zeros from so that I can analyze the data.

# ? May 12, 2010 17:36

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Find and replace 0 with nothing, make sure you check off the "match entire cell contents" box.

# ? May 12, 2010 18:08

Jagershot: Jun 7, 2004; RIP Mike V, 1989-2007. Have fun mounting Bear Bryant up in heaven.

ZerodotJander posted:

Find and replace 0 with nothing, make sure you check off the "match entire cell contents" box.

Wow, that's so simple I feel completely stupid for not thinking of it. I literally spent two hours at work deleting zeros a month ago.

Thanks a ton!

# ? May 12, 2010 18:21

mobby_6kl: Aug 9, 2009; by Fluffdaddy

I've been asked to help out with some VBA code. My function needs to (among other things) check if each of the cell values in one column is present in another column, and return the ones that are found as a range. So I did that in the most obvious way (I'm not very familiar with VBA and excel programming) and just loop through everything, using Union to add to the range once another cell is found.

Everything worked fine, until it turned out that the other column can contain as many as 6k rows, at which point my extremely naive algorithm of course starts to choke and take forever to complete. So before I start implementing something much more complex, is there a way to use some of the built in Excel functions, which I assume would be more optimized already? Countif, for example, seems to be able to very quickly check if one of the values is found, but I don't know how to neatly use it with the rest of my function. TIA.

# ? Jun 10, 2010 16:45

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

=IF(MATCH(A2,$B$2:$B$26,0),A2,"")

Column A has your values and Column B is the list you want to check for column A values. If it evaluates to #N/A, that's not a match and you can delete it. Or you can use IFERROR to force #N/A to evaluate to 0 or something like that.

# ? Jun 10, 2010 17:33

ZerodotJander: Dec 29, 2004; Chinaman, explain!

All that does is return the value if it is present in Column B. Is Column B sorted in any way?

# ? Jun 10, 2010 18:18

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

ZerodotJander posted:

All that does is return the value if it is present in Column B.

Isn't that what he needs? Maybe I don't understand the question.

Edit: I think I see. You want to actually return the range where the match is found?

TheAngryDrunk fucked around with this message at 02:19 on Jun 11, 2010

# ? Jun 11, 2010 02:15

mobby_6kl: Aug 9, 2009; by Fluffdaddy

TheAngryDrunk posted:

Isn't that what he needs? Maybe I don't understand the question.

Edit: I think I see. You want to actually return the range where the match is found?

Yeah, I need to return the range of the column A cells which were found in column B (could be in another sheet or workbook). Sorry if I phrased the original question poorly.

The data are numeric IDs, and are mostly sorted in column B but this can't be relied on unless we do the sorting. This isn't really an algorithm question as I could just use binary search, which I think would be fast enough. I mainly wanted to avoid reinventing the wheel and overcomplicating the macro if some built in functions could do the job.

Which, thanks to the suggestion above seems possible. I also found that somebody already wrote a function that searches a given range for a specific value, and returns the range of the cells where it was found.

Add a column with the IF/Match function
Use the existing function to search for the range where a match was found
Offset the range and delete the coulumn

So I'll try this unless there are any other faster/more elegant suggestions.

# ? Jun 11, 2010 09:07

ZerodotJander: Dec 29, 2004; Chinaman, explain!

If it's not sorted, I suppose you could use COUNTIF to determine how many total instances of the value there are, then use a loop to run match functions with ranges of decreasing size to find all of your cells.

# ? Jun 11, 2010 13:47

ufarn: May 30, 2009

This is in Google Docs Spreadsheet, but here goes.

The height of the cells seem to follow no particular system; a cell table with the exact same fonts can have different cell heights for no apparent reason. How do I make, and keep, these uniform?

# ? Jun 14, 2010 18:02

IT Guy: Jan 12, 2010; You people drink like you don't want to live!

Hoping someone can help me out here because I'm no Excel guru.

I have a user implementing a pop-up calendar control on several date cells so that anyone who opens the workbook can click the cell and click the date from the calendar.

All works fine until you share the workbook, then it pops up with an error box "Run-time error '1004': Unable to set the Left property of the OLEObject class"

If I unshare the workbook then everything works again.

# ? Jun 23, 2010 12:56

Acethomas: Sep 21, 2004; NHL 1451 684 773 1457

I have a commission chart I would like excel to check against to return the commission rate onto a big box on the front of my chart.

Basically I have the current percentage to goal and I need excel to check that against the chart, which is something like this

0 to 80 | 0%
80 to 100 | 5%
100 to 120 | 10%
120 to 150 | 15%
150 + | 20%

Any chance anyone could help me on this one, I guess I need to know how to format my chart so excel can read it and how to make excel look at it.

# ? Jul 11, 2010 17:24

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

Your table should look like this:

0 0%
80 5%
100 10%
120 15%
150 20%

Your formula is: =VLOOKUP(D1,A1:B5,2,TRUE)

Where D1 is the sales amount you want to look up.

This also assumes that the commission percentages apply to all sales once a new level is reached. For example, 5% on all $100 sold. Not 0% up to $80 and then 5% on the $81-100.

# ? Jul 11, 2010 17:48

Acethomas: Sep 21, 2004; NHL 1451 684 773 1457

Actually the numbers are percent to goal of total sales, so my sheet adds all of my sales against my monthly goal and tells me my percentage, and when that percentage is within those amounts I get te listed comission

# ? Jul 11, 2010 19:53

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

Not sure I follow. Can you provide real world example with the numbers you've listed?

# ? Jul 11, 2010 21:15

Acethomas: Sep 21, 2004; NHL 1451 684 773 1457

As of right now for this month my total sales are 132% to Goal, this means my team is currently getting 15% commission. This changes on a daily basis. This number controls the amount of commission my team can get. So anywhere from 80-100% to goal gets you 5% commission, etc. So I need look at that percentage (in this case 132) and then look at where that falls on the chart (80-100, 100-120, 120-149, 150+) and have it return the amount of commission that corresponds with that value on the chart (5, 10, 15, and 20. In this case 15). That way I can show each team member how much money they are getting in commissions right now. I have the whole chart set up fine except for having the chart automatically know the current rate of commission from our sales to goal.

# ? Jul 12, 2010 02:45

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

Doesn't the formula I provided you do that?

# ? Jul 12, 2010 03:48

Acethomas: Sep 21, 2004; NHL 1451 684 773 1457

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.

# ? Jul 12, 2010 17:35

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.

# ? Jul 12, 2010 17:45

ufarn: May 30, 2009

EDIT, figured it out because I'm an idiot.

ufarn fucked around with this message at 16:37 on Jul 17, 2010

# ? Jul 17, 2010 16:34

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

ufarn posted:

I tried doing this with this code: =IF((B4*B5)>5000, THEN 10%*B4*B5, OTHERWISE 15%*B4*B5), but I get a parse error.

Are you actually including THEN and OTHERWISE? I don't think you need that, if you are.

# ? Jul 17, 2010 16:36

ufarn: May 30, 2009

TheAngryDrunk posted:

Are you actually including THEN and OTHERWISE? I don't think you need that, if you are.

Exactly. Just found an article that slapped me around about it. Thanks.

# ? Jul 17, 2010 16:38

Grey Area: Sep 9, 2000; Battle Without Honor or Humanity

Is there a way to do sprintf() or String.format() in VBA (Excel 2003)? Using lots of & gets unreadable fast.

edit: Any idea why this gives a 1004 error:

code:

yearClassAvgPrice = "=IF(SUM(Totalt!$C$7:$C$23)=0;0;SUMPRODUCT(Totalt!$C$7:$C$23;Totalt!$D$7:$D$23)/SUM(Totalt!$C$7:$C$23))"
SummaryOrigin.Offset(7, 5).Value = yearClass1AvgPrice

Pasting the formula into a cell works.

Grey Area fucked around with this message at 07:41 on Jul 22, 2010

# ? Jul 22, 2010 07:13

Sub Par: Jul 18, 2001; Dinosaur Gum

Grey Area posted:

Is there a way to do sprintf() or String.format() in VBA (Excel 2003)? Using lots of & gets unreadable fast.

edit: Any idea why this gives a 1004 error:
code:
yearClassAvgPrice = "=IF(SUM(Totalt!$C$7:$C$23)=0;0;SUMPRODUCT(Totalt!$C$7:$C$23;Totalt!$D$7:$D$23)/SUM(Totalt!$C$7:$C$23))"
SummaryOrigin.Offset(7, 5).Value = yearClass1AvgPrice
Pasting the formula into a cell works.

You've got semicolons in the if statement that should be commas. When I paste that in, Excel tells me about it being wrong. When I change the semicolons to commas, it tells me there's something else wrong that it can auto-correct. I'm too lazy to compare your original to the auto-corrected, but here it is:

code:

=IF(SUM(Totalt!$C$7:$C$23)=0,0,SUMPRODUCT(Totalt!$C$7:$C$23:Totalt!$D$7:$D$23)/SUM(Totalt!$C$7:$C$23))

But beyond that, you're setting the value of the cell to "yearClass1AvgPrice" where the variable you're storing that string in is just "yearClassAvgPrice" with no 1. That's the runtime error.

Sub Par fucked around with this message at 14:58 on Jul 22, 2010

# ? Jul 22, 2010 14:56

ufarn: May 30, 2009

In Google Spreadsheet, how do I create a chart pulling data from two columns that aren't next to each other? Obviously, the A1:F6 syntax does not work here.

# ? Aug 2, 2010 21:20

unixbeard: Dec 29, 2004

Is there a way i can generate a fixed content range? I have a series of observations from a time series and want to call SLOPE() on them. The observations are the y-vals for SLOPE(), and for the x vals i just want 1,2,3,4 ... 20. Is there anything like range()/xrange() in python?

# ? Aug 6, 2010 15:30

logiryan: Feb 20, 2004; The meatball's a rollin'

I need to enter the zip code (column G) and have it automatically fill in the specific City (column E) & State (column F) from a database in the second sheet. What would the formula be? I have looked online on the Microsoft support but I don't understand the formulas.

Thank you!

# ? Aug 18, 2010 01:04

ZerodotJander: Dec 29, 2004; Chinaman, explain!

You probably want to use VLOOKUP.

The syntax is =VLOOKUP(lookup_value, target_range, target_column, FALSE)

Your lookup table has to be arranged so that your index (Zip) is in the leftmost column. So for example, if you have a table like this

code:

Zip       City       State
12345      A          Z
12346      B          Y
12347      C          X
12348      D          W
12349      E          V

Located in cells A4:C9, you could do

=VLOOKUP(G2, $A$4:$C$9, 2, FALSE)

to look up the value in cell G2 in range A4:C9 (dollar signs added so you can fill down without changing the range), find the first occurence of that value (FALSE tells it to look for an exact match) and return the value in the 2nd column (City).

# ? Aug 18, 2010 02:46

SvK: Feb 20, 2010

ZerodotJander posted:

You probably want to use VLOOKUP.

The syntax is =VLOOKUP(lookup_value, target_range, target_column, FALSE)

Your lookup table has to be arranged so that your index (Zip) is in the leftmost column. So for example, if you have a table like this
code:
Zip       City       State
12345      A          Z
12346      B          Y
12347      C          X
12348      D          W
12349      E          V
Located in cells A4:C9, you could do

=VLOOKUP(G2, $A$4:$C$9, 2, FALSE)

to look up the value in cell G2 in range A4:C9 (dollar signs added so you can fill down without changing the range), find the first occurence of that value (FALSE tells it to look for an exact match) and return the value in the 2nd column (City).

Be careful on how many cells use VLOOKUP if you have several of them based off one another on a few hundred thousand rows of data it can really slow down processing time. If you notice a slowdown and you do not need the data to be 'refreshed' from the 'database' than copy & paste special values over the VLOOKUP ranges to save time.

# ? Aug 18, 2010 04:21

Long Wang: Aug 28, 2006

VBA/Excel question

At work I have to deal with a file which is generated by a till which to the untrained eye is just a jumble of meaningless numbers, but actually contains product numbers, transaction numbers, cashier id, credit card numbers, etc. I need to check that this file is correct.

I've come up with the idea of using a VBA script which parses this data, puts things in different colors so they become more human readable. This works pretty well.

But the problem I have is, the file is essentially a text file. To open it and run a parsing script and I need to open it in Excel, convert it into an XLS spreadsheet, then import VBA code before it does it's magic.

Essentially my VBA script is almost a way of allowing Excel to open and display a new file format. It would be great if I just could click File | Open and it would automatically parse the file.

Is there any way to do this?

# ? Aug 18, 2010 19:48

Nippashish: Nov 2, 2005; Let me see you dance!

I have a table of data which looks like this:

code:

Date       Item    Cost
20-Aug-10  Hats    $20.00
20-Aug-10  Bats    $10.00
20-Aug-10  Cars    $55.00
21-Aug-10  Coats   $13.00
21-Aug-10  Boats   $17.00
22-Aug-10  Stoats  $31.00
22-Aug-10  Goats   $21.00

... and so on. I would like to be able to sum up daily (and weekly/monthly/yearly/etc.) totals from this data, so for daily totals I would end up with:

code:

Date       Total
20-Aug-10  $85.00
21-Aug-10  $30.00
22-Aug-10  $52.00

There can be any number of transactions per day (and some days might be missing). This seems like it must be a common problem, but I haven't been able to find anything via google.

Edit: I've found the "subtotals" tool which can do the reduction I want, but it puts the results inline with the original data which is really not suitable.

Nippashish fucked around with this message at 00:12 on Aug 29, 2010

# ? Aug 28, 2010 23:26

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Use SUMIFS.

# ? Aug 29, 2010 00:26

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

SUMIF or a pivot table.

# ? Aug 29, 2010 00:47

Nippashish: Nov 2, 2005; Let me see you dance!

I got it working with SUMIF. I actually tried that earlier but like an idiot I was passing the wrong arguments to SUMIF.

# ? Aug 29, 2010 02:17

gwar3k1: Jan 10, 2005; Someday soon

Long Wang posted:

Is there any way to do this?

Have a template, that when opened, automatically displays the Open File dialog then execute the code from there. Have the user double click the template rather than the text file.

Are the files sequentially named or somehow named in a way that you could automatically grab the most recent file and open it? That'd minimize input further.

# ? Aug 29, 2010 21:54

Adbot: ADBOT LOVES YOU

# ? Apr 24, 2024 09:53

factorialite: Mar 3, 2008; by Lowtax

I have a crazy question.

I've created an excel file with the each nfl team's schedule on it, and on each page I also have a ranking system. In this file, I've created a tab called Alpha Tab that has a column for each team and a column for ratings (so you can put your own rating system there). I want to use a function to lookup the name of whatever tab I'm in and pull the value to the right of that name in the tab "alpha tab."

For example, I am in the Miami tab. I want to write a function that says "I am in miami, let me go to the alpha tab and return the value just to the right of miami."

Is this possible? The spreadsheet makes heavy use of calling value from other tabs, so I know that can be done. However, I did it using brute force, and it'd be nice to learn how to do this.

Thanks in advance!

# ? Oct 9, 2010 00:14

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

«‹›82 »