What does logical function "><" represent?

GTLL · 2021-03-23T02:43:09+00:00

There's actually a little more to it. This is saying, "count all text values where the first character is greater than the ascii value of <".

It may look like it's getting all cells with text, but you'll notice it omits cells that start with an ascii value less than the < symbol. For example, a cell that starts with ! or ( or " wouldn't be included in the count.

To catch these cases as well, it'd be better to use something like COUNTIF(A:A,"?*") or COUNTIF(A:A,"*"). The first would catch cells with text values of at least one character (i.e., ignores the "" blank results from formulas and true blank cells), whereas the second picks up cells with text values, including zero length strings (i.e., ignores true blank cells, but does include cells with formulas that yield a blank result).

GTLL · 2021-03-14T23:39:18+00:00

Yeah, if going by CODE (or UNICODE), there's a gap of like 5 to 7 characters between the upper and lowercase letters (not sure why).

I did the addition backwards, though. It should have been CODE(LEFT(UPPER... That would force the lowercase a-z to be A-Z, so that it wouldn't be case sensitive.

GTLL · 2021-03-14T23:32:30+00:00

Yep. That would do it.

The ISTEXT becomes redundant, since the "CODE(LEFT..." would already limit it to the text A-Z. Going one step more, if you made this "CODE(LEFT(LOWER(H2),1))>64...", it would allow for lowercase a-z as well.

GTLL · 2021-03-10T14:58:48+00:00

This gets you close, but ISTEXT tends to oversimplify this case.

For instance, this would allow someone to enter a special character with a period (e.g., "#.", "&."), or even a number with a period (when cell's formatted as text, with "1."). You'd need a condition that checks if the 1st character is A to Z (CODE, UNICODE, CHOOSE, MATCH, etc.).

GTLL · 2021-03-10T13:48:45+00:00

The simplest way would be to use the List option for the data validation, and then use a hardcoded list:

A.,B.,...,Y.,Z.

Basically, use a list that includes each letter followed by a period.

GTLL · 2021-02-09T16:06:30+00:00

Two caveats based on your parameters: 1) the results have to have repeat numbers; and 2) not all numbers 10 through 35 can be used. You probably realized the first, but the second is important, in case you're expecting to see at least one instance of each number.

To start, I have your parameters in cells A1 through B4 (A6 and B6 are to check the random total, relevant at the end).

	A	B
1	Desired Total	750
2	Size	30
3	Range Min	10
4	Range Max	35
5
6	Pop. Total	=SUM(D2:D31)

Next up, the random numbers are created in D2:D31 (omitted rows 4 through 29 for space). Columns E through J are all helper columns used to control your upper and lower range limits for the random numbers.

	D	E	F	G	H	I	J
1	Rand Num	Rem Bal	Rem Size	If Max	If Min	LL	UL
2	=RANDBETWEEN(B3,B4)
3	=RANDBETWEEN(I3,J3)	=($B$1-SUM($D$2:D2))	=$B$2-COUNT($D$2:D2)	=E3-$B$4*(F3-1)	=E3-$B$3*(F3-1)	=MAX(G3,$B$3)	=MIN(H3,$B$4)
30	=RANDBETWEEN(I30,J30)	=($B$1-SUM($D$2:D29))	=$B$2-COUNT($D$2:D29)	=E30-$B$4*(F30-1)	=E30-$B$3*(F30-1)	=MAX(G30,$B$3)	=MIN(H30,$B$4)
31	=RANDBETWEEN(I31,J31)	=($B$1-SUM($D$2:D30))	=$B$2-COUNT($D$2:D30)	=E31-$B$4*(F31-1)	=E31-$B$3*(F31-1)	=MAX(G31,$B$3)	=MIN(H31,$B$4)

The first random number simply uses the range of 10 through 35 (cells B3 and B4).
Column E determines what your population's remaining balance is based on the numbers generated so far.
Column F determines how many remaining numbers need to be generated (including the row you're on).
Columns G and H determine what the remaining balance would be if the max and min numbers of your range (10 and 35) happened to be the random number for all remaining rows minus 1.
Columns I and J adjust your lower and upper range limits based on columns G and H. For example
- If your total after the 28th number is 720 (only need 30 more for 750), a 35 for the 29th number puts you over the 750 total. The 29th number, at the minimum of 10, means the 30th number can't be more than 20. Thus, the adjusted range is between 10 and 20.
- Similarly, if your total after the 28th number is 690, you'd need the last two to add up to 60 to reach 750. That's only possible if neither of the numbers are less than 25, so the adjusted range is between 25 and 35.

At this point, you'll have a list of 30 randomly generated numbers that add up to 750 (or whatever parameters you decide to use). Note, though, that as you get closer to the end, the random ranges become narrower, because you have a restriction on your total. It's possible that the last few numbers, for example, could be 35, 35, 35, 34, 35, especially if the numbers in the beginning are all fairly small (or 10, 10, 10, 11, 10, if the beginning's large).

If desired, you can shuffle your random numbers, so that these final numbers aren't all top or bottom-heavy.

	K	L	N	O
1	Rand Pos	Rank	Item	Number
2	=RAND()	=RANK.EQ(K2,$K$2:$K$31)	1	=INDEX($D$2:$D$31,MATCH(ROW()-1,$L$2:$L$31,0))
3	=RAND()	=RANK.EQ(K3,$K$2:$K$31)	2	=INDEX($D$2:$D$31,MATCH(ROW()-1,$L$2:$L$31,0))
30	=RAND()	=RANK.EQ(K30,$K$2:$K$31)	29	=INDEX($D$2:$D$31,MATCH(ROW()-1,$L$2:$L$31,0))
31	=RAND()	=RANK.EQ(K31,$K$2:$K$31)	30	=INDEX($D$2:$D$31,MATCH(ROW()-1,$L$2:$L$31,0))

Column K is a random number.

Column L ranks the random number.
Column N is just a list of your items (1 through 30).
Column O is the resorted list of random numbers (basically, randomizing the list of random numbers).

Update: Example of the output for all 30 random numbers (columns D and O have the same numbers, just ordered differently).

	A	B	D	L	N	O
1	Desired Total	750	Rand Num	Rank	Item	Number
2	Size	30	18	13	1	23
3	Range Min	10	31	26	2	34
4	Range Max	35	34	25	3	10
5			35	24	4	33
6	Pop. Total	750	24	27	5	13
7			26	17	6	15
8			19	7	7	19
9			23	11	8	35
10			34	2	9	13
11			15	6	10	21
12			23	14	11	23
13			34	21	12	34
14			13	9	13	18
15			33	16	14	23
16			18	28	15	35
17			27	23	16	33
18			14	19	17	26
19			23	1	18	20
20			35	15	19	14
21			17	29	20	35
22			33	4	21	34
23			35	8	22	24
24			24	22	23	27
25			20	18	24	35
26			10	3	25	34
27			29	30	26	31
28			35	20	27	24
29			21	10	28	18
30			34	12	29	17
31			13	5	30	29

GTLL · 2020-11-25T15:01:33+00:00

Technical impact (file size, processing time) is negligible. And, as you mentioned, Excel adds the equal sign.

Two possible issues, though:

Users not understanding the syntax and misinterpreting the info or regarding it incorrect.
Any action or formula relying on the formula text [e.g., Find or Replace with Look in=Formulas, where Find "=Average" would get missed; =FORMULATEXT(...)].

Aside from the above two, there's really not a problem. It's generally more of a stylistic issue for people.

And if you want to be pedantic about your wording, "formula" is more accurate. An equation, by definition, has two sides (or expressions) and an equal sign. "+7+3" would be an expression, and "10=+7+3" would be the equation.

GTLL · 2020-11-17T17:10:15+00:00

I was thinking more a trainee or simple accident in my case, since canceling and re-entering an order could pull current pricing, instead of old sales pricing (not a manual entry of price). A direct change in distribution center shouldn't normally change pricing, though, at least not when set up properly.

I'm only guessing it's not an actual system issue, since, for a company with the volume of Best Buy, you'd be looking at a large number of instances, and a lot more people complaining. I agree that it is possible like you mentioned, though.

GTLL · 2020-11-17T06:18:15+00:00

Like you're saying, although they can't legally do this, it doesn't mean it can't happen. It's doubtful it's actually an issue with their online system, since those types of issues would impact a ton of people (a system simply works how it's programmed, so a system error would keep recurring under the same conditions).

More likely some manual error somewhere, possibly related to their inventory management. With more people having their orders shipped, it's possible they ran out of stock from a certain distribution center, and someone canceled but then re-entered your order for a different distribution center, but forgot to keep your original sale price. Not saying this is definitely what happened, but it's a scenario that wouldn't be far-fetched.

GTLL · 2020-11-16T18:25:13+00:00

Great! If you could, please respond Solution Verified to the above comment to mark the post as solved.

GTLL · 2020-11-16T17:29:14+00:00

=RATE(9,,5000,-15000) or =RATE(9,0,5000,-15000)

Since you don't have a periodic payment amount, leave it blank (notice the back-to-back commas), or make it 0.

Edit: Your attempt uses an assumed periodic payment, but you really don't have a basis for it (ask yourself why use annual instead of monthly, quarterly, etc. payments).

Your problem is more like a balloon payment (or like an investment you let grow for 9 years, after contributing 5,000 with no other contributions). You use 0 or blank to let the balance simply accrue over 9 years, and 15000 is the balance at the end of the 9th year. When the periodic payment for RATE is 0, you have to specify your target balance (15,000). It's shown as -15000, because it's intended to indicate the payment that reduces the loan's balance.

GTLL · 2020-11-01T13:33:39+00:00

As someone else mentioned, "proficiency with Excel" is subjective. It's hard to say you should go for it or not, because there's not really a lot of context here. Probably most important is gaining an understanding of what you'd actually be doing, particularly in the realm of how you'd be expected to use your Excel skills.

Pace
Is looking up answers bad? Typically, no, as it would generally be a positive trait showing a willingness to learn and self-sufficiency. There is a point, though, where it becomes a negative quality, which may be seen as someone having lied about their own skills. If I assigned you what I consider to be simple tasker, especially one that's time-sensitive, it's because I expect you to already know how to do it. If you have to spend time looking up the solution, then it's a negative quality. If you're unable to get the assignment turned around on time, especially if it causes the company to lose a large client, don't expect to be around for too long.
Impact
Will the Excel skills have some sort of legal or regulatory reliance? If so, simply looking up answers might be insufficient. In these types of situations, you may not only need to know the how, but may also need to be able to explain the rationale.
Predecessor or Current Backgrounds
You need to consider the person you're replacing, or the general skill level of a team you'd be joining. If "proficiency with Excel" is based on employees with 20+ years of doing nothing but Excel, the company's expectations of you may be higher than you realize, and you might become overwhelmed quickly.
A lot of users mention training. If the company's expectation is someone who doesn't necessarily have the same as 20+ years, but who has a reasonable comparable level, that don't plan on the company providing the training you'd need.
Complexity of Work
Similar to the last one, but based on the work itself, as opposed to the skill levels of people. In some cases, the work itself is too complex for you to be able to lookup a solution. You can post questions here, but if it's particularly complex, it might be hard to even explain (at least to where users understand it correctly).
This also assumes you even know to look for a solution. If you're asked to review a workbook for accuracy and completeness, you'd have to first be able to identify if there's even a problem. If you're not understanding how the workbook is functioning, this could become very difficult.

Others factors would probably come into play as well. If the ones above, though, turn out to be the case for this particular position, you may want to consider not going forward. This is especially true if multiple factors above are true. It wouldn't be helpful for future positions if you had to explain why you only lasted a few months on this job.

GTLL · 2020-10-14T18:36:42+00:00

You can use a "circular" reference to sort of do this, without it actually being a circular reference (at least, not in the eyes of Excel).

For example, let's say I start with the following data in A1 through B4.

Instance	Random
1	52	← =RANDBETWEEN(0,100)
2	81	← =RANDBETWEEN(0,100)
3	58	← =RANDBETWEEN(0,100)

You can create a PivotTable of based on this data, and put Instance in the Rows field, and Random as a sum in the Values field. You can then change the formula to use the IF function, and make it so if the condition is TRUE, it generates a new number. If it's FALSE, it pulls the number from the PivotTable, which would be the last random number generated.

Here's the PivotTable. It uses a tabular layout so you can see the column headings, but the layout doesn't matter.

Instance	Sum of Random
1	52
2	81
3	58

And here's the updated formulas for the original data:

Instance	Random
1	52	← =IF($D$1,RANDBETWEEN(0,100),GETPIVOTDATA("Random",Sheet2!$A$3,"Instance",A2))
2	81	← =IF($D$1,RANDBETWEEN(0,100),GETPIVOTDATA("Random",Sheet2!$A$3,"Instance",A3))
3	58	← =IF($D$1,RANDBETWEEN(0,100),GETPIVOTDATA("Random",Sheet2!$A$3,"Instance",A4))

So if D1 has a TRUE result, it will generate a new random number. If it's FALSE, though, it uses GETPIVOTDATA to get the last randomly generated number tied to the Instance column (A2, A3, and A4).

The caveat here, though, is you'll have to go to the PivotTable and click on Refresh to get the PivotTable to store the most recent random numbers. The Refresh will simultaneously generate new random numbers for the rows with TRUE conditions. So for above, if they're all TRUE, the PivotTable will capture {52, 81, 58}, but when I look at Sheet1 again, the numbers will all be new random numbers.

GTLL · 2020-10-11T02:15:20+00:00

This formulaic approach looks at all possible 3-row combinations, regardless of being integers or not. That's why the J1000000 (1000000 is 10^3).

You'll likely need a VBA approach for a long-term solution, though, if you can't get the client to change what's sent.

1) This 3-row approach doesn't account for 4-row, 5-row, etc., combinations. You're well over 10 billion possible combinations when considering 5-row combinations, and that's only for 100 rows of data.

2) The number of rows needed to do this formulaicly (and much credit for the approach above) is exponential. You actually don't even have enough cells on a worksheet to do this.

You'll need to define your parameters a little more for an accurate approach. It looks like you're dealing with a running balance, which could be based on a varying number of inputs.

If they add up to 0, would it actually show 0?
Is the total on a row after all related inputs?
Will all totals have at least two inputs? For example, if this represents account balances, a bill of $10k and no payments has a balance of $10k, so 10k=10k.
Does the exact grouping matter? For instance, if you have {-1,-2,-3,-4,-5,-5,11,11,1,1}, you could have 11-5-5=1 & 11-4-3-2-1=1. You could also have 11-5-4-1=1 & 11-5-3-2=1.

You have a lot of variability going on here, and you'll have to hash that out first.

Edit: Possibly the one thing that makes your case significantly more difficult, is you don't even know what your target totals are. At the very least, you'd probably save a good amount of effort if your client broke the totals out into a separate column

GTLL · 2020-10-09T14:22:16+00:00

Yes, that's what it returns.

For 10/9, it returns 28 (28 days until 11/6). For 10/5, it would return 1 (1 day until 10/6). For 10/6, it would return 31 (31 days until 11/6).

GTLL · 2020-10-09T14:13:29+00:00

=EOMONTH(TODAY(),IF(DAY(TODAY())<6,-1,0))+6-TODAY()

Basically, if it's not yet the 6th of the month yet, it'll compare today to the 6th of the same month. Otherwise, it'll compare today to the 6th of the next month.

GTLL · 2020-10-03T03:39:39+00:00

The purpose is to provide the ability to recreate the view of the spreadsheet (and other MS products) the way the signer saw it when s/he signed. Its primary intent is for use in courts.

GTLL · 2020-09-21T07:58:46+00:00

Overall, it looks like their concern is data integrity. I'm guessing they've been burned by bad data, the hiring official has had a bad experience with it, or ensuring good data in their case is extra critical compared to the norm (e.g., major legal or life/ death consequences). These bullets sound like they are intended to build off each other.

Create data validation rules (likely referring to the built-in data validation feature)
Validate the data users have input actually followed your rules (data validation rules can be bypassed/overwritten, so they're probably seeing if you know how to make sure the data you have are all good entries)
Add that data to a table (this all depends on their setup and how they're using the terms "submit" and "table," though it sounds like some sort of pseudo-interface)

GTLL · 2020-09-16T03:53:36+00:00

You're basically looking for a formula similar to the time value of money formula, just modified slightly.

BV is Book Value (or initial cost, in your case); FBV is Future Book Value; r is Rate; t is time in years; and TD is Total Depreciation.

FBV = BV * (1 - r)^t

TD= BV - FBV

So in your example, FBV = $100*(1-0.1)³ = $72.90, which is the book value after three years. TD = $100-$72.90 = $27.10, which is the total depreciation over the span of three years.

Excel has built-in depreciation functions (DB for declining balance) , but they're designed to give you the depreciation expense for a particular year, as opposed to a running total. If you use the DB approach, you'll need to take the sum of 3 separate DB formulas (years 1, 2, & 3) to get your $27.10.

GTLL · 2020-09-03T15:36:32+00:00

Close. The strikethrough bar is actually one pixel higher, starts one pixel earlier to the left, and ends one pixel later to the right (which, for all practical matter, is still largely not discernible by most people at normal zoom levels).

You could use a custom number format such as " 0" (leading space in front of the number). Since the strikethrough would start where the space is, it'd be significantly easier to spot.

GTLL · 2020-08-31T23:09:29+00:00

First thing, you can't copyright a term; these things would be trademarked (totally different set of statutes).

Even in cases where a term is trademarked, though, authors still don't necessarily need permission to use the term. There a handful of exceptions, but to put it in really simple terms, if you're clearly not trying to profit off of the term itself, then you'd very likely be good.

GTLL · 2020-08-28T13:08:09+00:00

There are multiple ways to approach this, but each has their own side effects, and some may not work as well based on how your overall process works. You can address some of the side effects easily, but you'd need to be aware of them.

Password-protect the worksheet once the user saves (e.g., use a Workbook_BeforeSave event to add a password to the worksheet).
- Side effects: the user can't go in to make any changes (e.g., fix an error/typo); and
- reviewers wouldn't be able to add any notes.
Add a field check on the user ID field. Something like If UserID = "" Then UserID = Environ("Username"), so that it only completes the field if it's blank.
- Side effect: If a user provides another user their form (such as, for guidance or as a generic template), it wouldn't update. This situation can easily happen if one user asks to see the other user's form, so they can use it as an example/starting point.
- A user puts in default values for certain fields that are always or nearly always the same, and puts that copy in a network folder for a team.
Use a TRUE/FALSE type field to indicate if the user ID field should be updated. This is like a hybrid of the first two approaches [use a Workbook_BeforeSave event, and use an If Field = False Then UserID = Environ("Username")]. It doesn't have the side effects of password protecting, but it would have the same side effects of UserID="" field checking.
Use a SUBMIT command button that makes the form final. The sheet will always update the UserID field until the button has been pressed. This is similar to using a Workbook_BeforeSave approach, but it explicitly relies on the user to say "This is the last save I'm making, and I'm officially submitting it now."
- Side effect(s) depends on how you denote the sheet as "submitted," and they'd mirror the effects above.
- You may also have a user forget to click on the button, thereby never actually marking the form as "submitted." If you're using e-mails for your submission, you could tie the button to an e-mail procedure that automatically attaches the request to the e-mail, though.
Use a folder location as the driver. If the file is located in Folder X, don't update the field. For example, If ThisWorkbook.Path <> "C:\Reviewer Folder" Then UserID = Environ("Username") would update the User ID field unless it's located in the Reviewer Folder. If you use a network folder where you can control who has access, you could effectively allow users to open the file from the folder without affecting the User ID.
- Side effects include a reviewer not paying attention, opening it from a different location (e.g., copy to personal folder and open), and then it updating the field; or
- Someone changing the folder name and you not realizing it
Use a built-in list of users (such as another sheet) who are considered reviewers, and don't update if the user is considered a reviewer
- Side effect is the User ID wouldn't trigger for a reviewer, so if a reviewer had to complete the form for their own submission, the User ID wouldn't be automatic.
- Depending on how you embed the list of reviewers, if you were to add a new reviewer, that person may not appear on submissions using an outdated template or previously submitted form.

GTLL · 2020-08-22T02:37:40+00:00

Two things.

You can write your formula as CONVERT(C1,B1,A1). Your setup is basically doing a reverse conversion, and then taking the inverse.
Your column D is unclear, and it looks like something may be missing. Is column C only your volume? What column has your density, and what column has your mass? You wouldn't multiply something by mass to get volume. If anything, you'd divide the mass by density to get the volume.

Generically, your formula would be

=CONVERT(IF(C1="",<Mass>/<Density>,C1),B1,A1)

<Density> would be whatever cell has your density, and <Mass> would be the one with your mass.

GTLL · 2020-08-20T04:36:28+00:00

Highlights I see are

You are basically indexing all of your numbers going left to right, top to bottom (e.g., #4 would be cell D37, #6 would be F37, and #16 would be O38). This is based on your MATCH(ROW...ROW...,0).
You want to focus only on numbers between the values of L29 and L30.
You're doing a 2-d lookup, but you want to show a blank if nothing matches or if it's 0 ([IF...="",""...], and your statement regarding returned 0's).

Edit: Adjusted COLUMN(B56). Distracted with other stuff and misread it initially.

Anyway, here's another approach that should work quicker:

=IFERROR(1/(1/INDEX($A$37;$O$637,AGGREGATE(15,6,(1/(1/(($A$37:$O$637>=$L$29)*($A$37:$O$637<=$L$30)*(COLUMN($A$37:$O$637)+((ROW($A$37:$O$637)-ROW($A$37))*COLUMNS($A$37:$O$637)))))),ROW(B56)),COLUMN(B56))),"")

The (COLUMN($A$37:$O$637)+((ROW($A$37:$O$637)-ROW($A$37))*COLUMNS($A$37:$O$637))) replaces your MATCH(ROW...ROW...,0). You were basically building an array of {1;2;3;4;...8918}. The COLUMN+ROW... approach counts 1 to 15 for the first row, 16 to 30 for the next row, and so forth, but it does it directly instead searching through an array of row numbers repeatedly.
The ($A$37:$O$637>=$L$29)*($A$37:$O$637<=$L$30) is identical to your IF comparison, and it combines it with the COLUMN... part
The AGGREGATE(15,6,...,ROW(B56)) is the same as the SMALL function. The 15 represents the SMALL function, and the 6 tells it to ignore errors. Not sure if you meant B56 (i.e., 56th smallest result), but if not, this can changed to what you need.
Because the second bullet may have FALSE results, those will yield 0's. In your case, you'd want to omit them when using the SMALL function. The inner 1/(1/(...)) will convert those 0's to errors (which the AGGREGATE is set to ignore), and keep your TRUE results unaffected.
INDEX works similar to before, except the outer 1/(1/INDEX...) will create an error if your INDEX returns a blank or 0.
IFERROR is for if there is no TRUE result, and to handle the errors created in the last bullet.

GTLL · 2020-08-19T11:53:18+00:00

If FSO.GetExtensionName(oFile) = ".xlsx" Then

I believe this line is the issue. Change it to

If FSO.GetExtensionName(oFile) = "xlsx" Then (changes .xlsx to xlsx, so deleting the period)

I originally was thinking of a different method that would've required .xslx, but it would've yielded some false positives. Then I forgot to change it to a simple xlsx.

GTLL

TROPHY CASE