Replies

Hi Maud,

I hope you aren’t buried in whatever amount of snow/units were being predicted.

I’m in NJ about 2 miles from the Atlantic and the snow turned to rain sometime over night (I got home at 1am when it began snowing – no I wasn’t at a bar, but turned to rain by the time I got up at 8am). The predictions for here were much worse.

So hopefully the predictions for you were worse than the actual snow fall.

Fred

Hi Maud,

I will admit to not opening your file when posting my original comment but by going by your description of what’s in a row and what’s in a col. To me, your response even seemed to support your original description.

But now that I’ve looked at your original file, it is as I thought it was.

What I was talking about was something like Row 1 with the following cols and then entering the data for each day-hour in a separate row (versus a cell in a table as you have):
Date Weekday Hour #Customers
20170314 Tuesday 1200 3
20170314 Wednesday 1300 4

this would easily allow adding another column for amount spent. Maybe even another col for #personnel on duty, any promotions, etc.

My problem in doing any further work is that pivot tables and graphing are my weak points in Excel. But this seems like a “typical” data base kind of application.

Fred

Maud,

Having had these kinds of needs in the past, I usually proceed at first to set up things like you suggested. And then I find that I get burned when my analysis requirements go beyond what I thought of originally.

So I end up redoing the setup as a data base. In this case, I’d have a col for date, another for time/hour (maybe using a 24-hr clock for better sorting by hours), a 3rd col for #customers. If wanting to analyze customers by day of week across a year, another col could be added using a formula to get the day of the week. The analysis could be done by time of day across all days. Etc.

Then the analysis could be done by pivot table or by graph or by formulas. Additional cols could be added for things not envisioned right now (which I have finally learned will always happen).

The total number of rows for 1 year doesn’t seem so much 365×24 (or some smaller # hrs). Doing this might also make the data entry easier (rather than having to read down the correct col and across the correct row to enter the # of customers for that day/hour.

Just a thought while Zeddy is pondering a solution.

Fred

Thanks again, Paul.

In fact, when I tried the original macro with some hidden text, including a point value in brackets, but not displaying hidden text, I got an error from the macro. So I’ve changed it as you had it in your revised macro and it works fine.

Fred

Hi Paul – thks for the followup.

I added the points in the Do While loop by adding i to a counter named Test_Pts. Your way of doing it in the Do i loop may be a little more efficient but I wasn’t 100% sure of using the ArrPoints matrix.

I see you put in a Font.Hidden = False. While I understand that, my trials showed that the Find process was skipping that anyway, so I think I’m ok.

But I also noticed you changed the .Format attribute from False to True when adding the Font.Hidden. Is there a reason for that? Why not leave it as it was (w/o the Font.Hidden even)?

Multiple choice questions? Never!

Fred

Hi Paul,

Thanks so much for the macro. My macro writing skills are not so rusty after all.

I’m not sure what you read in my first post and I apologize if I sent you in the wrong direction. What I said in that post:

Where to find the points for each question:

The number of points allocated to each question is enclosed in square brackets, so something like [3]

Conditions on what might be inside the square brackets:

If what’s enclosed between the brackets is not numeric or it’s more than 2 digits (more likely even more than 1 digit), then that occurrence should be skipped

And another condition:

if the font of the square brackets is hidden, then the number should be ignored

What the macro should do subject to above conditions:

search thru an entire document (usually not more than 4-5 pages) and add the numbers between square brackets

Final Display of info:

At the end, it would display in a msg box the total number of points it found and maybe the number of questions it found

So your macro does what I first asked for plus the distribution of points among questions.

I was able to add a few lines to get the TOTAL number of points in the test per the original post.

Fred

Paul – I forgot to mention in my last post that my attachment was only a small part of a test. In this case, there were no 1-point questions. In other parts of an actual test, there certainly could be 1-point questions (not usually though). But there certainly could be questions worth 2, 3, 4, or 5 points. That is why I didn’t see why I’d search for [1] and [2] and [3] … since the F/R dialog using your first Find What string already gave me the number of questions. To get the number of points, I’d have to find the number of questions worth 1 point and multiply by 1; # questions worth 2 points and multiply by 2, etc and add up all the results. That’s why I originally asked for the macro.

So I understand that searching for the #cases of at least 1 point would give me the number of questions (but I didn’t see how to do that in one step). But that is really secondary to the number of points allocated to ALL questions; the # questions is nice.

When making up a test, a teacher wants to make sure that he/she has allocated 100 (or whatever the test is worth) points. If you’re off by a point or two, you adjust point allocations of existing questions (and want to make sure you’ve done this correctly – hence the need to re-check the point allocation). If you’re off by as many as 3, 4, or even 5 points, you add/delete questions.

Fred

Paul – I’m not sure what you’re seeing. My attachment had
question 1 worth 3 points, so [3] appears to the left of the question # and question, dealing with “The numbers on uniforms…”
question 2 worth 4 points dealing with “The numbers on uniforms…” (I just duplicated question 1 so I could hide a question as a test)
question 3 worth 2 points dealing with “The amount you owe…”

NOTE that the bold type at the top “Classify the data sets …” is not a question. It is common information or instructions that apply to the following questions.

So I’m not sure how the 2 occurrences (forgetting question 2) shown by the F/R dialog adds to the 5 points these 2 questions are worth.

Based on the attachment and original posting, what I was looking for was something like “You have 2 questions worth 5 points” or “You’ve allocated 5 points over 2 questions”. Recall that hidden questions are not to be counted.

Perhaps the misunderstanding here is what gave rise to the search string in your original response of [1] ???

Fred

Hi Paul,

The “reading highlight” option in the F/R dialog does not, per se, exist in Word 2003. Instead, there is a check box “Highlight All Items Found In” which seems to accomplish the same thing. With the box checked, I can then select “Main Document” in the drop down below the check box (which is enabled only when the box is checked). From what I can tell, the “reading highlight” option was a button that was introduced in Word 2007 (and the check-box-plus-drop-down feature deleted, which, IMHO, was a step backwards since the 2003 feature allowed more flexibility).

Anyway, with the box checked, the F/R dialog does tell me the # of occurrences found. However, this is not adding the # of points, which really is the more important information – having the # questions was just an extra nice-to-have to know how many points were allocated over how many questions.

As to the [1] comment, maybe that was my misreading of your first response:

Similarly, for a count of the questions (which presumably all have at least a single point), simply use:
Find = [1]

Per the bold highlighting in your response, I read that as using the find string of [1], or with the brackets, would tell me the # of questions with at least 1 point, although for the life of me I didn’t understand that. To me, that would just give me the # of occurrences of questions with 1 point. I’d have to repeat that with a change for 2, 3, 4… point questions. That is not what I’d want to do nor give to others.

I don’t have the points set with auto-numbering and they are in the format mentioned. I’m attaching a sample file to show how the test is structured.

Thanks.

Fred

Hi Paul,

Thanks for response. A couple of things:

– it looks like there was something lost in your response in the 3rd line (“with the and Word”)

– I’m doing this in Word 2003, haven’t tried 2010+ yet. So don’t know if results will be different. Assume not (although I know MS changed the short cut key for CTRL+f for something else)

– with the first Find to add points, Word did highlight the occurrences of [3] or whatever the points were throughout the doc. But it didn’t add the values. It did even ignore cases where [3] was formatted as hidden (I sometimes delete questions from the file by hiding the question in case I decide to bring it back in the future).

– with the second Find to add the # questions, I got “The search item was not found” for both variants of your suggestion (w or w/o wildcards).

-Lastly, while I’m not 100% sure that a macro is needed (despite some of the items above), I suggested a macro bcs I anticipate difficulties with others using the Find approach in just typing the “Find What” string (I know you could store the string and copy-paste it into the “Find What” box) and remembering to check the “Use Wildcard” box (I just forgot to do it myself when doing a 4th try with your approach). So maybe a macro is needed just for “ease of use.” Further, as one is constructing a test, adding/deleting questions and changing point values, one might want to run the “add the points” process many times. While the search string would “survive” while the Word document was open (even if closing the Find dialog), if one closes the Word doc and resumes editing later, having to type the search string again becomes error prone. Further, one has to change the string to get the # of questions and then back to get the # of points.

So for the above, I suggested a macro.

Fred

It may be worthwhile to reset IE settings at Control Panel, Internet Options, Advanced tab, Reset (bottom right).

Thanks Bruce.

Not sure what resetting will accomplish. I do understand that doing that should restore any Advanced settings I may have changed to the default value. And I probably have changed a few settings. But I don’t see any settings that would seem to pertain to the problem I’m having. That being the case, how would resetting that option (wherever it is) or any other setting under the Advanced tab address the issue?

Further, I know I changed some settings a while ago but the “typing problem” is more recent.

Happy Belated July 4th to you too.

Great time here on the Jersey Shore.

What – no macro-based spreadsheet with exploding fireworks?

Fred

Maud,

I just tried your new version. Very cool.

I almost didn’t see the narrow button next to the Snap button. Could be worsening eye sight with advancing age.

Very cool that the grid allows comparison of different dispersal strategies. I assume it also allows different sales to be saved too but I didn’t trying changing the original sales. I guess when you “snap”, the grid stores the current sales values in row 4 and the current cash flows from row 5.

Fred

Hi Maud,

Fred, Maybe you didn’t try to save when the percentages didn’t add up to 100%.

You’re right. I had thought the error msg would come up as soon as the totals showed other than 100%. I didn’t include “less than” 100% because that’s certainly possible while you’re first filling the percentages. Of course, no single month could be negative or greater than 100 since those are not choices in the drop-down box. So I can see the error msg being useful only when you go to Save the form. But certainly it’s possible to catch the error as soon as it occurs, rather than waiting for one to save the form, if a % selection sends the total over 100. But I don’t think it’s a big deal.

Fred, I played with this extensively and I believe my code calculates spot on.

I looked at your very cool “arrow” diagram showing the calculations.
– First, if there’s some tool you use for this, please advise where to find since I could use that for lots of things. If not, you sure spent a lot of time making that diagram – very nice!
– Second, my calculations in my previous post did agree with your arrow diagram. And my method did agree with it too. BUT I downloaded a fresh copy of your previous version (the one I had commented on) just to be sure my copy was not messed up. See attached screen shot on what I got for April (the 46 for April, rather than the 41 which I calculated and your new version is getting).

I knew Maud could do it!

A few comments:
– I changed the # months to 6, decreased one of the month’s allocation, and put the remaining % in month 6. I observed that the current allocations did not change. I would think that given a set of sales, one might want to play around with the monthly allocations to see how the cash flow is affected. That is, once the dispersal form is saved, the cash flows in row 5 should be updated. But that is for the O.P. (original poster) to comment on.

– I also observed, while changing the # dispersal months to 6, that I could put in a % into month 6 that created a sum > 100% (even with the remaining % to be allocated now shown in red). Again, it should be clear to a user that this should not be. But it would seem a warning about allocating >100% should be issued and, perhaps, you NOT be allowed to save the form – especially if the %s are used to update the cash flows per above.

– Lastly, with the current allocations as created in Maud’s file, I’m not sure if the cash flows are adding up correctly.
— For Jan, sales = 100. So for Feb, cash flow is 15%*100=15. No problem.
— For Feb, new sales=20. So for March, cash flow should be (Jan’s sales with Month 2’s allocation) + (Feb’s sales with Month 1’s allocation). Assuming this is correct, that gives 30%*100 + 15%*20=30+3=33. No problem.
— For March, no new sales. So for April, cash flow should be (Jan’s sales with Month 3’s allocation) + (Feb’s sales with Month 2’s allocation). This should give 35%*100 + 30%*20 = 35+6 = 41. Results are showing 46 ????

I could also see, perhaps as part of my first dash, that once a set of dispersals is saved, that the %s should be put into some (protected?) set of cells so one can see how the cash flows change as the % allocation changes WITHOUT having to call up the form. Not that calling up the form is hard, but it could be desirable to copy/paste the info to another sheet showing dispersal %’s, sales, and cash flows for some subset of months. Again, something for the OP to comment on.

I may have some time over the next few days to create a non-VBA solution along the lines that Maud did. There would be a set of cells to enter %s into, like Maud’s form, and a check to see if they add up to 100%. The cash flows could then be calculated, perhaps using helper (hidden) rows, as suggested in my previous post. Whether this is still needed, in light of Maud’s excellent solution, is something that I’d wait on guidance from the O.P.

Fred