|
do it posted:I have no real knowledge of Excel, but managed to hack this together: 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.
|
# ¿ Feb 13, 2010 03:31 |
|
|
# ¿ Mar 29, 2024 15:46 |
|
mistermojo posted:Ok, so I use this function to count across a row: 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.
|
# ¿ Mar 25, 2010 19:43 |
|
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.
|
# ¿ Mar 25, 2010 19:47 |
|
unixbeard posted:i manually enter a bunch of date, number values across 2 colums, e.g. =sum(indirect("B5:B7")) treats "B5:B7" as text, so it will always use that range and keep it from updating.
|
# ¿ Apr 5, 2010 16:40 |
|
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. 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 |
|
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 |
|
factorialite posted:=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.
|
# ¿ Oct 14, 2010 06:09 |
|
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.
|
# ¿ Jan 4, 2011 22:19 |
|
Ivan Drago posted:Very simple question (I think): Try something like "=index(C:C, match("Jim", A:A, 0)+3)" Index() can also change columns if you need it.
|
# ¿ Feb 2, 2011 00:36 |
|
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:
|
# ¿ May 15, 2011 03:58 |
|
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:
|
# ¿ May 24, 2011 19:23 |
|
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?
|
# ¿ May 26, 2011 16:58 |
|
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?
|
# ¿ May 26, 2011 19:34 |
|
TraderStav posted:Currently the code is: 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 |
# ¿ May 26, 2011 21:04 |
|
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.
|
# ¿ Jul 1, 2011 19:10 |
|
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.
|
# ¿ Jul 10, 2011 19:06 |
|
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:
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.
|
# ¿ Jul 20, 2011 18:04 |
|
HClChicken posted:I'm confused with how I should program this vlookup formula. =vlookup(A_target_value,indirect(vlookup(age,MA,2,True)),2,True) Is that what you meant?
|
# ¿ Jul 21, 2011 00:39 |
|
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. 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 |
|
FuzzyBuddha posted:I suspect the following request is fairly easy, but I'm kind of an Excel neophyte. 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 |
|
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 |
|
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: 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:
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 |
|
Heavy_D posted:I've seen esquilax's post but isn't it just easier to do I read his post as him having his values all in one row instead of two columns.
|
# ¿ Nov 4, 2011 23:38 |
|
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. 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.
|
# ¿ Feb 22, 2012 15:50 |
|
GregNorc posted:Is there something to SUMIF() that I'm missing? Use COUNTIF(). SUMIF() tries to add all the values that equal M, and "M"+"M" = 0
|
# ¿ Mar 26, 2012 21:47 |
|
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)?
|
# ¿ Aug 8, 2013 03:26 |
|
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. 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.
|
# ¿ Aug 8, 2013 21:54 |
|
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.
|
# ¿ Aug 9, 2013 00:31 |
|
melon cat posted:Is there an easy way to make Excel calculate and display ratios? 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)
|
# ¿ Aug 13, 2013 03:28 |
|
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. 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.
|
# ¿ Jan 24, 2014 01:50 |
|
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: 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:
code:
|
# ¿ Jan 27, 2014 23:50 |
|
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".
|
# ¿ Jan 28, 2014 17:46 |
|
Old James posted:What about a formula? 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.
|
# ¿ Jan 30, 2014 18:08 |
|
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. Do you know VBA? Try something like this. Note the function InStr() is case sensitive. code:
|
# ¿ Jan 30, 2014 21:48 |
|
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: There are a few types of stacked bar charts. You picked one that always adds to 100%. Right click -> Change Chart Type -> Stacked Column
|
# ¿ Feb 11, 2014 23:04 |
|
melon cat posted:A question about creating charts using number data. 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 |
# ¿ Mar 5, 2014 21:58 |
|
melon cat posted:Unfortunately that solution isn't working for me. It looks exactly the same. 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.
|
# ¿ Mar 5, 2014 22:54 |
|
Loving Africa Chaps posted:I've got what im sure is an easy problem i can't sort out by myself. 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
|
# ¿ Mar 31, 2014 22:34 |
|
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! 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:
esquilax fucked around with this message at 20:32 on Apr 7, 2014 |
# ¿ Apr 7, 2014 20:28 |
|
|
# ¿ Mar 29, 2024 15:46 |
|
Oops, yep.
|
# ¿ Apr 7, 2014 22:52 |