The Swamp

A_B wrote: Tue Oct 29, 2024 3:45 pm If you don't have to do further manipulation a copy(or cut)/paste then sort would be the easiest.

I have to do it like 65 times though. Wanted to see if there was a less manual shortcut.

Johnny Carwash wrote: Tue Oct 29, 2024 3:48 pm

A_B wrote: Tue Oct 29, 2024 3:45 pm If you don't have to do further manipulation a copy(or cut)/paste then sort would be the easiest.

I have to do it like 65 times though. Wanted to see if there was a less manual shortcut.

Got you, I figured that so I gave with the "easiest" way but not the least work-intensive while I was noodling.

You can reference the cells (A7=B4), but you'd have to do some manual entry there as well so it isn't very elegant. And then the other option would be some macro/VBA shit I can't even recall how to do at the moment.

This may be promising but also isn't "easy" if the syntax isn't perfect:

Enter below formula in cell A8 and copy down:


=IFERROR(INDEX(A$2:G$5,INT((ROW(A1)-1)/COLUMNS(A$2:G$5))+1,MOD(ROW(A1)-1,COLUMNS(A$2:G$5))+1),"")



You may start at any cell. Just update the range "A$2:G$5" per your requirement. Ensure that in the start cell you enter "ROW(A1)" as in the above formula which appears twice.

So that last one worked for me. I tested it with numbers and text. I just copied that text from my browser to the excel function box. To see it on screen, here is the link, and it's almost all the way down:

https://answers.microsoft.com/en-us/mso ... bc33c5f711

Actually just found a way to do it in Power Query. Thanks though.

Ryan wrote: Mon Jun 03, 2024 2:20 pm Huh. I bet it just thinks the dates are fractions like usual and it's getting really lucky.

It stores dates as "days since Jan 1, 1900" so if you enter say Dec 31 1900, it calculates it as 366. Leap years be leapin'

Gunpowder wrote: Wed Oct 30, 2024 8:25 am

Ryan wrote: Mon Jun 03, 2024 2:20 pm Huh. I bet it just thinks the dates are fractions like usual and it's getting really lucky.

It stores dates as "days since Jan 1, 1900" so if you enter say Dec 31 1900, it calculates it as 366. Leap years be leapin'

Was 1900 a leap year?

Steve of phpBB wrote: Wed Oct 30, 2024 8:30 am

Gunpowder wrote: Wed Oct 30, 2024 8:25 am

Ryan wrote: Mon Jun 03, 2024 2:20 pm Huh. I bet it just thinks the dates are fractions like usual and it's getting really lucky.

It stores dates as "days since Jan 1, 1900" so if you enter say Dec 31 1900, it calculates it as 366. Leap years be leapin'

Was 1900 a leap year?

It wasn’t!

Then not sure why it stores that as 366 and not 365.

Today I learned that years divisible by 100 are not leap years unless they are also divisible by 400. Anyway Excel counts 2/29/1900 as a day.

It's actually days since 12/31/1899.

Gunpowder wrote: Wed Oct 30, 2024 8:39 am Today I learned that years divisible by 100 are not leap years unless they are also divisible by 400.

I still remember that from when I had to program a date calculation way back in my very first programming job after college. And that was pre-widespread usage of the Internet (and we didn't have a live connection at that job), so I have no idea what resource I got that info from.

Shirley wrote: Wed Oct 30, 2024 9:09 am

Gunpowder wrote: Wed Oct 30, 2024 8:39 am Today I learned that years divisible by 100 are not leap years unless they are also divisible by 400.

I still remember that from when I had to program a date calculation way back in my very first programming job after college. And that was pre-widespread usage of the Internet (and we didn't have a live connection at that job), so I have no idea what resource I got that info from.

As a kid I read books full of that kind of nerdy trivia.

Any way to copy values from non-consecutive ranges and have them appear in the same cells when you paste?

Ryan wrote: Wed Jan 15, 2025 12:12 pm Any way to copy values from non-consecutive ranges and have them appear in the same cells when you paste?

I don't exactly understand the question, but there are some different options under "Paste Special" that may do what you are trying to do.

I too don't fully understand the question.

Trying to copy A1, A2, A3 and A7, A8, A9 from one sheet and paste them into matching cells in another sheet. Instead it just pastes them all together from A1 to A6. There are values in the middle that I want to skip and not overwrite in the second sheet.

In the paste special box is skip blanks checked?

Oh ... I don't know of a way to post a range except for some sub-range in the middle. I think you'd have to either do it multiple times or set up cell references in the ones you want populate and skip the ones you don't.

Ryan wrote: Wed Jan 15, 2025 12:28 pm Trying to copy A1, A2, A3 and A7, A8, A9 from one sheet and paste them into matching cells in another sheet. Instead it just pastes them all together from A1 to A6. There are values in the middle that I want to skip and not overwrite in the second sheet.

instead of a copy can you do a formula
so in your destination you set a value to =sheetname(a1). Then copy that to the destination fields you want and skip the cells you don't. The formula should update the source cells automatically (if you don't pin the a1 using $a$1 for example). Not my syntax for the formula could be off but if you do = and then just select the source it will build the forumla for you

the you just copy and paste special value to remove the formula

I need to automate this spreadsheet to calculate response time, but as you see I run into errors when the dates are not the same. Is there a way where I can bring the dates into the formula =(F2-D2) and have it calculate accordingly?

I don't think you can do it in the format you have now ... there would have to be some sort of join between the dates (measure of days or 24 hours) and the time on the clock (which can be negative and subtract from full days).

=((F5-D5)+(G5-E5))*(24*60) would work in terms of giving you total minutes, just not in the hour:minute format.

mister d wrote: Wed Jan 22, 2025 3:18 pm =((F5-D5)+(G5-E5))*(24*60) would work in terms of giving you total minutes, just not in the hour:minute format.

Thanks. I concatted them into the same cell and got it to work.

Why can't I get this bottom row of data to update on my chart you guys?

Looks like the data just isn't in your chart.

Yes, no shit. Remind me how much I'm paying you again?

Use AI, bitch!







(Right click inside the chart, "Select Data" ...)

mister d wrote: Wed Feb 12, 2025 12:26 pm Use AI, bitch!

This isn't Excel, but I need a math nerd. Trying to calculate the medication error rate for FY'25 Q2 (Oct-Dec 24). It says that the error rate is number of errors per 1,000 bed days, but the number I have is based just on the amount of days in the quarter (3 errors in 92 days). Anyone able to derive the formula from the table below showing previous four quarters?


I am certain that the rate difference between Q3 (Jan-Mar 24) and Q4 (Apr-Jun 24) is due to Q3 having 91 days and Q4 having 92 days but I just can't figure out how to get there!

I don't think there's a way to make the math work given the numbers on the screen unless they're rounded. Q3 and Q4 both have 4, both had 92 days but are returning a different rate.

mister d wrote: Wed Apr 02, 2025 1:10 pm I don't think there's a way to make the math work given the numbers on the screen unless they're rounded. Q3 and Q4 both have 4, both had 92 days but are returning a different rate.

Oh yeah, they both had 91 actually but you're right...that has to be an error.

31 + 30 + 31 = 92, son

Q3 24 (fiscal year) is Jan-Mar of 24: 31, 29, 31

I bet whoever did that forgot it was a leap year so they calculated Q3 based on 90 days, and Q4 (Apr-Jun) correctly on 91.

Can you make any sense out of it that way?

Hmmm ... even still I don't know that makes sense. Q4 2024 is 91 days or could have been 90. Q3 2024 is 91. They should either be the same or Q4 should be higher due to the wrongly lower denominator, right? Unless this is a calc on weekdays or backs out holidays or who knows. I think you just have to ask whoever did it or find their formula.

They're gone, and these our residential congregate care programs so 365/24/7, but I don't need to resolve that error...just need to calculate the Q2 (Oct-Dec) error rate based on 3 errors over 92 days.

mister d wrote: Wed Apr 02, 2025 2:27 pmQ3 2024 is 91.

Are you just stating a fact or did you math that? I just need to know the calculations used to derive a .57 error rate based on 4, 91, and 1000.

Fuck your fiscal years!

Jerloma wrote: Wed Apr 02, 2025 3:15 pm They're gone, and these our residential congregate care programs so 365/24/7, but I don't need to resolve that error...just need to calculate the Q2 (Oct-Dec) error rate based on 3 errors over 92 days.

mister d wrote: Wed Apr 02, 2025 2:27 pmQ3 2024 is 91.

Are you just stating a fact or did you math that? I just need to know the calculations used to derive a .57 error rate based on 4, 91, and 1000.

I'm saying I can't back into the formula. There has to be one or more missing components.

Wouldn't the number of "bed days" change relatively randomly?

Just randomly picking a number (4 and 0.54), it appears that you had 7407 bed days in that quarter so if you use that again for 2025 Q2 and 3 errors, your rate is 0.405.

EDIT: Then again, in the 7 errors/1,15 rate quarter, you had 6,087 bed days. So that's just an estimate until you have data on that. The other quarter had 7,018 bed days (approx). If you're just being asked for an estimated rate, you can average those out or otherwise just pick a number in that range.

You can also use a line fit as an approximate formula, those numbers give you:

y = 0.163x - 0.025

y = 0.163(3) - 0.025 = 0.464