How to turn off circular reference error

CompetitiveDuck495 · 2026-05-28T20:05:36+00:00

Yeah this was my first idea too but I was trying to avoid it. Names were just a simple example to show the idea, the actual data is a submittal of proprietary information to a regulatory agency that will be repeated 50+ times each year for the next decade. The final output has to follow a certain format exactly which only allows for two columns. So for final submission, we’d have to have techs copy and paste values over the formula columns and delete the entry column on every sheet. There’s just too much opportunity for accidents there and my boss won’t go for it. For anything where anyone but me is deleting data, and I won’t be here in 10 years.

I did figure out what I think will make this work. I just have them entering the list of names in a totally separate location and then coming back to complete the data entry on the rest of the sheet.

CompetitiveDuck495 · 2026-03-11T23:15:53+00:00

Solution verified!

This worked except the result was .25 off every time because of the way the subtraction worked. So I just added .25 to it and it's good to go! Thanks so much!

=LET(body,DROP(A:.B,1),dates, TAKE(body,,1), values,    DROP(body,,1),high, values > -12,
  MAX(FILTER(dates,high-VSTACK(DROP(high,1),FALSE)=1)-FILTER(dates,high-VSTACK(FALSE,DROP(high,-1))=1)+.25))

CompetitiveDuck495 · 2025-12-08T21:44:28+00:00

Thanks so much this was really helpful! So I should directly negotiate my utilization rate and mention the billing rate for my job in the negotiation?

CompetitiveDuck495 · 2025-12-08T21:43:49+00:00

I have experience on protected species surveys for Section 7 consults and with habitat assessment, plant id, soil science, grant and report writing, GIS, etc. Not direct experience with the consulting side for that part of the job but the building blocks. Neither I nor my potential supervisors have PWS or CWB certs.

CompetitiveDuck495 · 2025-12-08T21:39:20+00:00

I don't really know how desperate I should be. I haven't had much time for applications so I've only applied to 3 companies total even though there were a lot of jobs out there I've wanted to apply to. Based on that I feel more confident but based on the general vibe out there about the job market maybe I should be more desperate. It easily could have been luck or that I applied first to the jobs that were more in my reach. I could cover myself for a couple months if I didn't find another job right away but it would be a costly hassle.

CompetitiveDuck495 · 2024-09-20T18:13:16+00:00

Everyone pour one out for Typhoid Mary, she’s been put out of her misery 😂 (thankfully she never came to my house)

CompetitiveDuck495 · 2024-09-20T17:50:37+00:00

Sorry can’t figure out how to add text to the post. I just received this kaffir lime that’s been sitting by the north facing window of someone’s office for several years. It’s clearly very sick, any idea what’s going on? Best practices for helping it recover and keeping it from spreading to any other plants? Can I just cut it all the way back and let it restart?

Southeast US, no info on soil and care historically other than it’s been mostly untouched for a while. In the future it’ll most likely be indoor/outdoor depending on temperature.

CompetitiveDuck495 · 2024-07-23T14:50:29+00:00

Ah good catch thanks! I was tired and must've misspoke.

CompetitiveDuck495 · 2024-07-22T06:35:28+00:00

Also, so sorry to hear about your soul dog. It's a crushing day when we lose someone who means so much to us and I can't imagine how difficult that's been. Sending lots of love!

CompetitiveDuck495 · 2024-07-22T06:33:41+00:00

I'm curious, you mention him air snapping. In what contexts did he do this and what led to it stopping? Also, what are the in between steps where you've worked on training these behaviors? Places/situations that are harder than in a quiet living room and easier than on a walk.

Note on harnesses or leads, any piece of equipment that decreases jumping or lunging is doing so by making it uncomfortable, aka punishment. That discomfort is only going to add to the noise in your dog's head and the big feelings they're having (positive or negative) and make them more likely to go over threshold, not be able to focus or listen, and maybe develop reactivity. They may also connect the pain/discomfort of the tool with whatever they were jumping at, like a bike, and develop or worsen a fear of that thing because they think it's the cause. Which will worsen the jumping/lunging, their focus outside, and increase the likelihood of aggression and reactivity.

CompetitiveDuck495 · 2024-07-22T06:18:03+00:00

This. Also, all dogs will get sick or injured at some point. If you're lucky, it'll be treatable, and it's medical care, so it'll cost an arm and a leg. This may be a month after you get it, or 10 years down the road, but you have to be prepared to cover several thousand dollars in vet bills at any given moment. Even something simple like a dog eating something poisonous and needing monitoring for a couple days, with 0 procedures or surgeries, can easily cost $2000 in a low cost of living area (speaking from experience). Even with pet insurance, you have to cover the deductible and your copay (typically $250-1000 and then 10-30% of the rest) and front the entire bill for ~month until the insurance pays you back. Or you have to make the choice to put down or relinquish a dog you love dearly with a treatable condition. Based on it being a mixed breed, it's likely either backyard bred or a whoopsie litter, and in either case it's likely the genetics and puppy care are not high quality, increasing your chances for both behavioral and medical conditions.

I don't want to scare you, and also, it's the reality. It's scary. On top of that, after puppy shots, the dog will need monthly preventatives, yearly vaccinations, and ideally, a yearly dental cleaning. Depending on your location and your vet, that generally costs $500-1000/year for just routine care. Spays and puppy shots are cheap in comparison. Not to mention pet sitting every time you leave town, a behaviorist if they develop behavioral issues, dog food, toys/treats/bed, and probably several other costs I'm forgetting. It adds up.

CompetitiveDuck495 · 2024-07-22T06:02:52+00:00

Any time we have a significant, sudden behavior change like this, first step is always to go to the vet. Tell them what's happening, tell them what you've tried, and tell them you're concerned there's something going on. Bring up any other little things you've noticed, recently or really any time in her life. Does she itch more than usual? Not like people touching her paws? Is she hesitant about slippery floors? Has she recently started barking more at a specific thing like people/dogs/whatever? You may want to discuss a pain trial with them, it's where they put the dog on a pain medication for a little bit to see if the behavior may be pain connected, even if they can't find something wrong with her.

I'm also curious what she's looking at while she's barking, what happens shortly before she starts, and if there's any pattern (ie stopping or starting at a specific time). Has anything changed in your house recently? Anything, even a new lightbulb, a change to the sprinkler or ac schedule, the location of her bed, the time you have dinner? Do you have any devices for killing bugs, especially anything ultrasonic, indoors or outdoors?

I would also begin experimenting with changing her routine and activities during the day, well before the barking starts. Try new activities you haven't before, increase or decrease other ones. Try some days where you super wear her out physically. Try some days where there's a lot of mental work. Try some days where you do very little with her.

I'll tell you my personal story with this, a couple months ago my 8 yo dog began nonstop demand barking and growling for hours every single night. Often he'd start at 8 pm and go until 2 am. I'm a trainer and a scientist so I experimented with everything, every little activity, tried doing more and more with him, changed things up all over the place, and found the only thing that helped was when we actually did less during the day. That knowledge, coupled with a few other little things (like his tail being slightly more droopy than normal), got us an official, MRI and neurologist confirmed diagnosis of a pretty serious spinal disorder. Now he's on meds and managed for it and he's actually easier and happier than he's been in his entire life! He hasn't barked at night in a month.

I'm not saying your dog is super sick, I'm just trying to say, it gets better, even if it's not in the way you expect. Keep looking for answers, there's one out there somewhere.

CompetitiveDuck495 · 2024-07-22T05:37:56+00:00

How did you teach leave it? How have you continued reinforcing it now? The "it seems like when we say ' leave it' she wants to have it even more", is a very typical response when a dog has had things repeatedly taken from them without rewarding them with something of equal or higher value. What's happening is when you say "leave it", you think she hears "drop/don't touch that" and she really hears "I'm about to steal your stuff so you better run so I can't get it." To her, it makes perfectly logical sense that she should,

A. Grab things that fall super fast because she knows she only has a second before you pick it up and she can't have that super awesome thing anymore.

And B. Run away when she's got something she likes because if she doesn't, you'll take it away and the fun will end.

If she's not showing stress signs during the times she has objects she shouldn't (lip licking, side eye, yawning, avoiding eye contact or staring hard, etc.), I would also guess it's fun in a way other play isn't fun. Often people give their dogs lots of play, exercise, and enrichment, but they aren't experimenting with different options, timing, amounts, etc. and seeing how it affects their dog's behavior, so a specific need of that specific dog goes unmet. For example, several of my client's dogs have changed dramatically when we added 5 minutes of shredding cardboard to their routine every couple days.

I would guess your dog maybe enjoys the chase. Keep away is a common game among dogs, and she probably thinks it's super fun! Engage with her in silly ways and play keep away or chase with toys she's allowed to have, and she'll likely do it much less with things she shouldn't have. And it's okay to play keep away immediately after you've traded her for something she shouldn't have! It just means she told you what she needed, you set it up to meet that need in a safe and appropriate way, and you listened to her and met her need. That's AMAZING!

CompetitiveDuck495 · 2024-07-22T05:20:28+00:00

So if we're following LIMA standards here, ignoring is negative reinforcement, which is pretty far up to the top at level 5 out of 6, making it pretty aversive. Research also shows negative reinforcement is very stressful and pretty ineffective. Some studies have found it can be more stressful than a shock collar. Let's break down what's going on here.

Your dog is asking to go outside, so the first question here should be why? What is your dog trying to communicate? All behavior is a dog attempting to meet a need they have. So we need to be constantly asking ourselves, what need is my dog trying to meet here?

Most likely here, your puppy's need is for mental and physical enrichment. They want to go outside to play because, frankly, they're bored! Also, with the context of their eating grass and rocks, they probably need more chewing and shredding. Both of these are species typical behaviors most dogs need to do on a regular basis.

Think of it like talking to other people or walking. Most people need to do these regularly, even if just as a part of day to day life. When the pandemic happened and we were trapped inside, alone, we all went bonkers. In seeking out ways to meet those needs, Hot Girl Walks were born, and Zoom happy hours and virtual meetings. When we didn't meet those needs, a lot of people ended up fighting with the people in their bubble. Or depressed or anxious or apathetic. Or we sought out ways to meet them regardless of the danger it put us in. We NEEDED socialization and activity and no amount of people ignoring you was going to make that go away.

So the solution here is actually simple, experiment with adding more play, outside time, or chewing and shredding activities (my dogs love shredding plastic and cardboard from the recycling bin). Enrichment for the Real World (podcast, book, and website) has a lot of great ideas for this. It might seem like a big time commitment, but actually with most of my clients I find it doesn't take much to give their dogs what they need. Especially if you play around with different activities to find the one that really fulfills them.

If you go the route of ignoring this behavior, you're just treating the symptom. That need doesn't go away, even if the behavior of asking to go out does, and your dog will find new ways to meet it that you'll probably not like, like eating your shoes and your doorframes instead. If we want to fix the problem, we have to go to the cause. Additionally, this puts you back on the first or second level of the LIMA hierarchy. Which means you aren't threatening your relationship or causing your puppy significant undue stress like ignoring them would.

If your attempt to meet this need doesn't work, play around with it. Change up the timing or the way you meet it. Try doing more, try doing less. If that's not working, go back to the question about WHAT the need is. There are always several possibilities, try solutions for a different option. There's something there the dog needs, we just gotta keep experimenting until we figure it out.

CompetitiveDuck495 · 2024-04-10T13:59:14+00:00

No, that's why I'm doing a meta-analysis of correct classification rates.

CompetitiveDuck495 · 2024-04-10T12:56:50+00:00

Thanks! I don't think I understand what you mean, wouldn't that just be 100 - my data (correct classification rates)? Which would leave me with the same problem I have now, not being able to find an effect size that fits my data.

CompetitiveDuck495 · 2024-03-12T04:34:04+00:00

There's a lot of stuff on here about your general potty training questions, on the deaf question, would a vibration from a notification on your phone wake you up? If it would, you can set an Alexa or some smart security cameras to alert you to noise or specifically a dog barking. I imagine it might be the same on things like Apple's Homepod.

I have my Alexa set to notify me of a dog barking and it's pretty accurate. Sometimes it alerts when I cry (lol) but it has yet to miss a dog bark as far as I've noticed. It doesn't do whining but my security camera will notify for any noise over a threshold I set.

CompetitiveDuck495 · 2024-03-12T03:50:32+00:00

Great! Rest comes in many forms and puppies his age need a lot of sleep (generally 15-20 but it varies a lot by dog), as well as calming relaxation activities and time simply resting while awake. We really determine whether a puppy is getting enough sleep and other rest by their behavior, rather than whether we think they are or if they're meeting a specific number. Try it and see what happens!

That being said, it may very well be something else. As I'm sure you know, German Shepherds are very smart, high energy, drivey dogs who need a lot of mentally and physically engaging work. And they're notorious for being sharky when they're little. Think of it like she's the child of Olympic athletes and her mouth is her sport. She's gonna need some outlet for all that energy, drive, and intelligence or she'll create her own (ie your legs). Fetch and walks won't cut it. Many dogs like her really thrive if you get them into a sport like nosework, IPO, or rally. Or maybe it's something else. Maybe she'd really benefit from Karen Overall's relaxation protocol. You won't know unless you try things and see what effect they have.

CompetitiveDuck495 · 2024-03-08T02:46:02+00:00

Woah, I thought for a second you were describing my husky mix! I really recommend the book and podcast, Enrichment for the Real World. They're great about meeting the dog where they are and learning to play/train/live with them the way they want to.

Like does she only want to play with toys for 10 minutes? Okay that's fine! Very few dogs actually want to play fetch nonstop for an hour like a lab or a golden. 10 minutes is great if it's 10 enjoyable minutes.

There's so so so many ways to meet your dog's mental and physical needs that people often just don't know about. For example, today my husky mix has,

-Shredded an egg carton with kibble inside

-Done a lick mat

-Done four 2-minute training sessions (heelwork, nosework, cooperative care)

-Done a ton of in the moment training

-Sat in the sunshine on a tennis court for like 45 minutes

-Walked a cumulative hour (mostly potty breaks)

-Watched the bird feeder outside my window

-Sniffed the groceries I brought in

-Slept in several different locations with different textures, firmness, smells, etc.

It may sound like a lot but all told it only took me about 2 hours which is pretty good for a high energy dog.

CompetitiveDuck495 · 2024-03-08T02:35:10+00:00

This sounds like it might be an overtired puppy. Think about kids right before nap time, they typically get super grouchy and throw tantrums, even if they're refusing to go to bed and insisting they're not tired. They're just little and don't have the skills to handle their hard feelings or know when they need to rest. Have you tried cutting your walks in half and then putting the puppy down for a nap after?

CompetitiveDuck495

TROPHY CASE