Replies

I don’t know if you saw my second post.

There is no need to move the data to start in col E if you use the revised formula of my second post.

You mentioned in post #3 that you wanted a single formula, so this will do it.

But with your data beginning in col C but you wanting the previous FOUR months, that creates a problem (referring back 4 cols) until you get to col E.

For example, if your data began in col C but you only wanted to go back 2 cols (sum up cols A and B), there would be no problem. If your data began in col E but you wanted to sum up 5 months, there would be a problem.

The problem, generally, is that you can NOT go back past (to the left of) col A.

From a purist point of view, the formula of post #5 is a bit messy. Compare the formula in post #4 vs #5 (when I was awake). The only difference is that -4 in the first one is replaced by that
-IF(COLUMN()<5,COLUMN()-1,4)

Note what the -IF does: if the column where you want to start the sum is less than 5 (columns, although usually identified by a letter, can also be referenced by the corresponding number – col A is 1, col B is 2, etc) meaning to the left of E, then we do NOT subtract 4 since that would take us back beyond the left of col A (which is NOT allowed, equivalently a col with a number of 0 or less). Instead we start the sum from col A (or the first col).

If the column where you want to start the sum is 5 or more (col E and beyond to the right), then the test
COLUMN()<5
is false, and the result of -IF will always give 4.

So if you look at the 100th or 952nd or 2500th col, the -IF is always FALSE and you're always starting the summing 4 cols to the left of the present column. At this point, you might wonder why the -IF is there, rather than just a 4.

The present column where you're putting the formula has a col given by COLUMN(). We don't have to actually know which column we're in to reference the current column.

So, I think your options are
1) move the labels to col D and start the data in col E; then you can use the formula in post #4 EVERYWHERE.
At this point, it could also be simplified to something like

SUM(A4: D4)/4 for col E.

As you fill to the right, Excel will adjust the formula to SUM(B4:E4)/4 for col F, then SUM(C4:F4)/4 for col G, etc.

2) if you want to keep the labels in col B, then
2A) use the formula in post #5 everywhere or
2B) use the formula in post #5 for just col D (and col C if you want) but then use the formula in post #4 from col E forever to the right.

As to the rest of the formula, the 2 INDIRECTs are putting together a starting point and an ending point for summing. Since these change with every column, you can't have a fixed column reference. That's what the COLUMN()-4 and COLUMN()-1 do. When added to the end of (concatenated with) the fixed string "R4C", you get something like R4C2 for a start and R4C5 for an end. (This is an alternative form of referencing cells called "RC" referencing, where you give the Row # after the "R" and the Col # after the "C" all as one string.)

Note that the 4 is because your sums are in Row 4. If you change your mind for this, you'd have to change the 4 to the proper row # (there are other alternatives than using a constant row # but those really don't seem to be needed here based on what you originally posted).

Hope this helps.

Fred

OK, it’s 5 minutes later and the brain is starting to function.

To adjust for the -4, try:

=SUM(INDIRECT(“R4C” & COLUMN()-IF(COLUMN()<5,COLUMN()-1,4),FALSE):INDIRECT("R4C" & COLUMN()-1,FALSE)) / 4

Personally, I think this is messy just to account for the fact of where you started your data. I'd move the column of labels to col D so that your first month's data is in col E. But that's me.

Also, if for whatever reason you decide to do away with the column of labels and start your data in col A, then the "-1" part towards the end would also become a problem. Sounds like that won't happen but I think you can see how to adjust the formula if you decide to do that.

Fred

how about a formula like:

=SUM(INDIRECT(“R4C” & COLUMN()-4,FALSE):INDIRECT(“R4C” & COLUMN()-1,FALSE)) / 4

Put this in row 5 (or some other row) and fill across.

The only (?) problem with the formula is that you can’t start it before column E (ie, it won’t work in columns A-D) because the “COLUMN()-4” part looks 4 columns to the left of the present column. There is no column 4 columns to the left of D.

An easy solution would just be to start your data so that Jan is in column E.

I think with a little more work, I could avoid that restriction also. Just need to work on the “-4” part a little. But it’s early here and my brain still isn’t fully awake.

Fred

If I’m understanding the overhead, it seems that your code is opening Excel and maybe even creating a workbook for every problem the student does. Could we take care of starting Excel when we open ppt/Word and open a workbook only at that time?

Then when the user does another problem, we already have an open workbook. I think I can see how to shuffle your original code to just take the answer to the current problem and pass it to Excel for evaluation. Could we make the part of your code that actually does the evaluation into a function callable from our VBA code?

With xlWkBk.Sheets(1)
.Range(“A1”).Value = StrInput
If .Range(“A1”).Text = “#NAME?” Then
If bEq = False Then StrInput = Mid(StrInput, 2, Len(StrInput) – 1)
EvaluateInput = “Invalid data: ” & StrInput
Else
EvaluateInput = .Range(“A1”).Value
End If
End With

Anyway, let us tinker with this for a while.

Also, in your last email you mentioned different forms of the sqrt(2) as

sqrt(2), 2^0.5, 2^(½), √2, 2 & 1.414

First, I’m assuming that 2 & 1.414 should have been 2 ^ 1.414.

I don’t think we have to worry about an input of “radical 2” since there’s no way to input that from a keyboard. Similarly, I don’t see an issue with the “half” character since that can’t be input either (but as a single character, are parens needed here?). But we could get “1/2” (hopefully in parens). And lastly, while valid, I don’t think anyone will input 2 ^ 1.414.

Hi Paul,

Doesn’t seem like a lot of code to me.

A couple of quick things:
– I gave the #NAME error as an example. I noticed you “trapped” that explicitly. How can we be more general?
– I also noticed you had an error for not being able to start Excel. Why would that be? Do we need Excel to be in a certain location on the “using” computer (where the ppt or Word VBA is running)?

Those are some quick thoughts. We’ll have a go at it and see what happens.

I kind of thought it would be possible and you’d be the one to figure it out. Thanks.

Fred

Vincenzo – you can play this 2 ways.

If you want to look like the hero all the time, don’t tell her about the Lounge and you’re set for life.

If you don’t want to bother with us (old) foggies, tell her to join the Lounge.

Fred

Hi Maud,

The explanation was for Vincenzo’s benefit to explain INDEX/MATCH. Anytime I use this combination, I have to think at least twice. It doesn’t fall trippingly off the tongue for me.

No problem – as I said, I had this in my bag of tricks from somewhere else thinking it would come in handy some time.

Vincenzo: attached is a revised spreadsheet with what you asked for. I chose to use the VLOOKUP function. It also could have been done with INDEX/MATCH. I decided to use VLOOKUP bcs I think it’s a little more straightforward than INDEX/MATCH; also your data layout was ok to use this. Had the original Ratings been to the left of the topics, VLOOKUP would not have worked.

Note that you have to be careful with any approach when extending this to the full solution.

Fred

Hey Maud – I think that’s what I did. I knew I saw this somewhere!

As far as having ranking values from 1 to 100, that won’t matter to the solution I posted.

As far as having a variable number of topics, I did not take that into account but it would not be hard to do. I’ll leave that for another time.

However, I would question having rankings of importance from 1-100, especially if there are only 10 topics. I’m not suggesting that you need to limit the “importance values” to 10 numbers just bcs you have 10 topics. But my experience has shown that people really can’t make much of a distinction between something that has an importance of, say, 87 vs 88. A small set of numbers should suffice.

Fred

Hi Vincenzo

Attached is a small workbook that does what you want without any macros.

RG: another saying (probably not Chinese nor Microsoft) – “to macro, or not to macro – that is the question.”

A few words of explanation:
– col C is present only to show you how the RANK function works “normally;” it can be omitted. Because of the possibility of tie-breakers, you want to use col D for ranking to create unique ranks. It uses the COUNTIF function added to RANK for this purpose. (I have to admit I got this from somewhere else.)

– You never stated whether 5 was most or least interest. I assumed that the higher rating meant more interest (5 is the most interest); if it’s the reverse, then you’d need to put in a third argument (any non-zero #) before the close parens of the RANK function in col D; eg, D2 would become =RANK(B2,$B$2:$B$10,1)+COUNTIF(…) (Also do this in col C if you just want to see how RANK works by itself.)

– This also uses the index-match combination that RG used. I use ROW()-1 in the way that RG uses the numbers 1,2,3, etc.
— I’m storing the sorted list of topics beginning at Row 2 so you can see how the sorted list lines up with the original list. ROW() gives you the current row #, so this is returning the numbers 2,3,4,… By subtracting 1, you get 1,2,3… as RG has. This is ONLY bcs I chose to store the topics starting in row 2. If you want to store the topics starting in row 13, as in your original spreadsheet, you’d do ROW()-12 starting in your location of F13. This also gives you the numbers 1,2,3, etc. Regardless of where you store the topics or using RG’s “hard-coded” 1,2,3, you need that 1,2,3 sequence. Basically, these 1,2,3 #s correspond to the topic with the most interest, the topic with the 2nd-most interest, etc.

— the MATCH function looks for the # per above in col D (the ranks with tie breaker). It returns the row within col D that has the value of ROW()-1

— the INDEX function takes that row # from the MATCH function and finds the topic in that same row in Col A.

Hope this helps.

Fred

Thanks RG.

I tend to copy/paste the column headings from the DB to the Criteria Range. I either do that or use references for the Extract Range.

I think my problem was the blank and duplicate column headings I had within my DB.

I don’t quite recall how I got it to work the first time. When I did it this last time, I used my tests sheet – where I do have blank cols and duplicate col headings. I thought I used my tests sheet before also but it could have been my quizzes sheet where there are neither blanks nor duplicates (I really don’t think so but…).

Anyway, I decided on kind of a compromise approach for now kind of inspired by the Advanced Filter approach.

My grades are stored on different sheets: one for tests, one for quizzes, one for projects, etc. So, using Advanced Filter would necessitate having a “setup” on each sheet – at least from what I can see. Each sheet has a different number of possible grades: quizzes have a max of 10, projects have 4, and tests have 3 or 4 depending on the class.

I originally took a brute force approach based on what I posted on Jan 1 and the formulas there. Every quiz (up to 10 even if I only gave 8 quizzes in the semester), every project, every test, etc. were present on the sheet (the one that I’d use to copy/paste to the Canvas spreadsheet) but only for ACTIVE students. This even though I would probably only be pasting one or two assignments (one quiz and maybe a project) after a given class. The file size, with formulas like I showed in post #1, grew to 1.2MB with no student data in it.

One of the reasons for the extra formulas was that I wanted all data for a given assignment in one contiguous set of rows ONLY for ACTIVE students. When I paste to the Canvas spreadsheet, it only has rows for ACTIVE students. Even if a student withdraws, I keep them in my gradebook where they were. So the quizzes and tests sheets have rows alphabetically for all students regardless of whether they’ve withdrawn or not. If I pasted my original data to the Canvas spreadsheet, I’d paste, say, 3 consecutive rows for 3 ACTIVE students, then skip the next 2 rows in my spreadsheet, copy another 2 rows for the next 2 ACTIVE students, etc (or just paste everything and then start moving grades up for only the ACTIVE students).

What I decided to do is just have 1 column for grades on the new sheet (call it the “Canvas Interface” sheet) rather than just 1 column for each possible grade. This is next to a column of names, which only has ACTIVE students. By way of a drop-down box and a small lookup table, I can say what kind of assignment (quizzes, tests, etc) I want and the number of the assignment (so Test 1 or Quiz 3). Using this info, I can fetch the grades for just the ACTIVE students for just that assignment. I can then paste these into the Canvas spreadsheet at one time. If I have a second assignment that was done that day, I just change the assignment type and number info and I’m ready to paste again. Then I import the Canvas spreadsheet into Canvas and I’m done – students can see their grades. My spreadsheet is now just 980KB.

Thanks for all the help.

Fred

Hi RG,

I think what you did would work. For some reason, I also had it working with my current layout when I tried Tom’s suggestion; the layout included some blank columns and some duplicate column names. Now I can’t get it to work – I get the infamous “The extract range has a missing or illegal field name.” I can’t quite figure it out but not sure it’s worth trying.

I was taught like you – everything on one sheet. I think as long as the Criteria and Extract Ranges are on the same sheet, it doesn’t matter what sheet they are on relative to the DB.

Fred

Tom – forgot to mention in my previous post: I’m running Excel 2003 (Version 11). Is version 10 the first version with Advanced Filtering?

Hi Tom,

Thanks for the suggestion. We just got back from a few weeks of vacation and have been slowly catching up with all that happened while we were gone.

One of the problems I’ve always had with Advanced Filter is that it’s not dynamic when using the copy to another location. I think in this case, that may not matter.

I ran a brief experiment and I can see that this could work. One question on the criteria:
– what I would prefer for some of the sheets needing filtering is a criteria that says to select those rows where a certain column contains something OTHER THAN the phrases “too early” and “withdrew” (w/o the quotes). I tried setting this up as “too early” in one column and “withdrew” in an adjacent column with the same heading. Didn’t work. So I resorted to having 6 rows for what the column could be and that worked fine.

Any suggestions to how to set up what I do want?

But for other sheets where those phrases don’t appear, I would have to resort to creating a new column with data from another sheet that provides the filtering criteria.

And it certainly does look like the size of my spreadsheet has grown quite large. I’d almost be willing to be that the Advanced Filtering would reduce the size of the file.

Thanks.

Fred

just in case column A had a ‘fred’

why would I be in col A?

Fred

Aha!

I didn’t quite get what you had meant in post #4 about the merge record #s. And yes, she wants the invoice #s to start with 100. I’ve done some field math in the past, so I know this can be done.

And yes, she does need to keep a copy of the receipts (she gets receipt books from some company that has been supplying them for years and years and they generate the next sequence #s when she orders more books). So she’ll either make a copy of the printed page before cutting it up or print 2 copies. Parents some times call in and ask about receipt #101 or whatever.

The one thing that was clear and I guess I’d have to go back to what you wrote earlier (the table and adding a row) is how to pick up with the sequence the following month. If we kept all the receipts in the workbook for all months, it would be easy since the rows for Feb would just pick up after the last # for Jan. Then we’d just need a way, as you said, to skip all the rows for old receipts.

One question: when skipping a record during the merge, does the next merged record get the next merge record # or does the skipped record take up that number as the next record from the data source even tho it gets skipped?

Thanks for all the help.

Fred