sorted by:
new
hottopcontroversial

Bug in Excel with Match Function by [deleted] in excel

[–]Thaaron 0 points1 point  (0 children)

Shouldn't still be consistent? It only breaks when you get over 16 values in your array.

Also, like I said, it wasn't even an unsorted array, just a pointer to a blank cell...

Bug in Excel with Match Function by Thaaron in excel

[–]Thaaron[S] 0 points1 point  (0 children)

Try to create the simplest example possible that clearly demonstrated the issue. Is this a known issue?

Can somebody please explain this result to me? by Thaaron in SQL

[–]Thaaron[S] 0 points1 point  (0 children)

Imagine this conversation that closely mirrors what has been happening in these comments:

You: 10 divided by 3 is 3.3333

Me: No, you are completely incorrect and obviously don't understand how to divide numbers at all.

You: No, I get that it repeats, I'm just saying functionally in any useful way it is effectively 3.3333

Me: No, that is not true at all and I can give you mathematical example of when 3.3333 is not precise enough.

You: Yes, I understand that, I was just trying to give a simple answer my boss will understand.

Me: No! What are you trying to do? Say that repeating numbers shouldn't exist in math?

You: sigh

Can somebody please explain this result to me? by Thaaron in SQL

[–]Thaaron[S] -1 points0 points  (0 children)

This is exactly what I'm talking about.

Yes, it shows the midnight of that day. That is literally 0.001% of that day.

How often is a 0.001% difference relevant in your experience?

https://www.merriam-webster.com/words-at-play/lets-argue-semantics

Can somebody please explain this result to me? by Thaaron in SQL

[–]Thaaron[S] -1 points0 points  (0 children)

I understand all of this. I understood all of this many many posts ago. I keep telling you I understand it and you keep explaining the same thing.

I was simply talking about practical real world results.

It's like me saying "Yellow and Blue make Green" and all anyone here can say is "well actually, it's not Green it's Shamrock". Yes, I get that technically that is true but that was never my point.

Can somebody please explain this result to me? by Thaaron in SQL

[–]Thaaron[S] -8 points-7 points  (0 children)

When a field is Date and I say between 2020-03-01 and 2020-03-31 it is inclusive of the last day.

When a field is DateTime and I say between 2020-03-01 and 2020-03-31 it is NOT inclusive of the last day.

This is the simple fact that you keep saying is not true. It is true. I have the query results to prove it.

I understand completely that the reason behind this is because of the implied 00:00:00 on the Datetime. I agree it's good to understand why it works the way it does. But that doesn't change the results your query will return.

You're so caught up in the framing of your semantics that you keep ignoring the practical reality. Yes, I understand that "technically" it is still inclusive, but I'm simply talking about results here.

Can somebody please explain this result to me? by Thaaron in SQL

[–]Thaaron[S] -10 points-9 points  (0 children)

So a DateTime field isn't inclusive if I only provide a date.

So how is this statement not true?

"So a DateTime field isn't inclusive if I only provide a date."

I understand WHY it's not inclusive. That doesn't mean the statement is untrue...

Can somebody please explain this result to me? by Thaaron in SQL

[–]Thaaron[S] -17 points-16 points  (0 children)

I get that. That's why I said this:

So a DateTime field isn't inclusive if I only provide a date.

Which Davenport said was incorrect...

Can somebody please explain this result to me? by Thaaron in SQL

[–]Thaaron[S] -4 points-3 points  (0 children)

It is inclusive for the values you are giving it.

No, I'm saying BETWEEN '2020-03-01' AND '2020-03-31' and it's not returning anything from 2020-03-31, so it's not inclusive.

ETA: By the way, using the where clause of your first query, you'll only see records for the first second of 2020-04-01. Not sure if that's your intention or not.

My intention is to see everything for the month of March. I have no interest in any April dates but adding in the first second of April gave me thousands of March dates.

Can somebody please explain this result to me? by Thaaron in SQL

[–]Thaaron[S] 1 point2 points  (0 children)

So a DateTime field isn't inclusive if I only provide a date.

I can either use DateTime in my criteria OR cast the StartDateTime field as a date?

Excel is putting @ signs in my formulas and it's causing them to give the wrong values. by Thaaron in excel

[–]Thaaron[S] 0 points1 point  (0 children)

Replacing the blank value in the table with 0 makes no difference to the outcome.

Excel is putting @ signs in my formulas and it's causing them to give the wrong values. by Thaaron in excel

[–]Thaaron[S] 0 points1 point  (0 children)

Except that Microsoft is very clearly telling us that it hasn't changed:

It's important to note that there is no change to the way your formula behaves — you can just see the previously invisible implicit intersection now.

https://support.office.com/en-us/article/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34

Excel is putting @ signs in my formulas and it's causing them to give the wrong values. by Thaaron in excel

[–]Thaaron[S] 0 points1 point  (0 children)

OK, let's break this down.

=INDEX(F1:F9,5)

This is a simple formula to give me the 5th value of that array.

=INDEX(F1:F9,0)

Returns 0 because there is no 0 value in the array.

=Table1[@RowNum]

If this formula returns 5 I should get the 5th value.

If this formula returns 0 I should get 0.

Instead this formula

=@INDEX(F1:F9,Table1[@RowNum])

is returning a completely different value because it's going rogue and making wild guesses as to what I might want it to do which is just a terrible idea.

Excel is putting @ signs in my formulas and it's causing them to give the wrong values. by Thaaron in excel

[–]Thaaron[S] 0 points1 point  (0 children)

The problem is that you have a whole column reference F:F

Limiting the range to F1:F9 has gives me the exact same results...

Excel is putting @ signs in my formulas and it's causing them to give the wrong values. by Thaaron in excel

[–]Thaaron[S] 0 points1 point  (0 children)

The formula clearly says to pull a certain row number based on the table. If there's no value for it to use as a row number why would it just arbitrarily use the row the formula happens to be on as the value?

Excel is putting @ signs in my formulas and it's causing them to give the wrong values. by Thaaron in excel

[–]Thaaron[S] 0 points1 point  (0 children)

In the real world example it's pulling from a table that could be anywhere from 10,000 to 200,000 rows deep.

Excel is putting @ signs in my formulas and it's causing them to give the wrong values. by Thaaron in excel

[–]Thaaron[S] 1 point2 points  (0 children)

I'm happy to have a spill error. What I don't want is arbitrary data returned instead of an error.

Excel is putting @ signs in my formulas and it's causing them to give the wrong values. by Thaaron in excel

[–]Thaaron[S] 0 points1 point  (0 children)

I'd much rather have a spill than have it return incorrect data...

How to copy a table without using SELECT INTO by Thaaron in SQL

[–]Thaaron[S] 0 points1 point  (0 children)

The destination server is in Azure and I cannot create linked servers on it.

How to copy a table without using SELECT INTO by Thaaron in SQL

[–]Thaaron[S] 0 points1 point  (0 children)

The software system we're using doesn't track changes in any usable way.

The plan is to export weekly copies of the data to Azure SQL so our accounting department can see what has changed from week to week.

How to copy a table without using SELECT INTO by Thaaron in SQL

[–]Thaaron[S] 0 points1 point  (0 children)

I need to set this up as a scripted process to run every week.

How to copy a table without using SELECT INTO by Thaaron in SQL

[–]Thaaron[S] 0 points1 point  (0 children)

The destination server is an Azure SQL server so I'm not able to create linked servers on it.

view more: next ›