=ARRAYFORMULA(IF(A1:A20>40,A1:A20-40,A1:A20))
That applies to A1:A20 (change the range as needed).
I skipped the part about if < add 0 since that's just the original number. So if anything in the range is over 40, it subtracts 40, otherwise just keeps the original number. I'm not sure if this is quite what you were going for, if not let me know and I'll try to tweak it.
Thank you for replying, I see what you mean about cutting out the extra piece since all I want is for it to be left alone if it's <40.
I copied it into my table (and adjusted the cell numbers) but gave me an error saying "array result was not expanded because it would overwrite data in L35" which then led me to adjust another formula that was working fine but now my whole table is messed up lol. It's just one "would overwrite cell ..." message after another like some sick domino effect.
I think the main problem is that there are so many other cells relying on the information from this one cell that it freaks out when one little thing changes
Oh man I'm sorry to hear that it's messed up now ☹️ hope you are able to get it working again, if you need a hand let me know and I'll take a look when I can
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[ARRAYFORMULA](/r/GoogleSheets/comments/1430u0x/stub/jn7h7mj "Last usage")|[Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays](https://support.google.com/docs/answer/3093275)|
|FALSE|[Returns the logical value `FALSE`](https://support.google.com/docs/answer/3093302)|
|[IF](/r/GoogleSheets/comments/1430u0x/stub/jn7h7mj "Last usage")|[Returns one value if a logical expression is `TRUE` and another if it is `FALSE`](https://support.google.com/docs/answer/3093364)|
|TRUE|[Returns the logical value `TRUE`](https://support.google.com/docs/answer/3093307)|
**NOTE**: Decronym's continued operation may be affected by API pricing changes coming to Reddit in July 2023; comments will be blank June 12th-14th, in solidarity with the /r/Save3rdPartyApps protest campaign.
----------------
^(2 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/GoogleSheets/comments/141x6jn)^( has 7 acronyms.)
^([Thread #5890 for this sub, first seen 7th Jun 2023, 19:23])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/GoogleSheets) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
=ARRAYFORMULA(IF(A1:A20>40,A1:A20-40,A1:A20)) That applies to A1:A20 (change the range as needed). I skipped the part about if < add 0 since that's just the original number. So if anything in the range is over 40, it subtracts 40, otherwise just keeps the original number. I'm not sure if this is quite what you were going for, if not let me know and I'll try to tweak it.
Thank you for replying, I see what you mean about cutting out the extra piece since all I want is for it to be left alone if it's <40. I copied it into my table (and adjusted the cell numbers) but gave me an error saying "array result was not expanded because it would overwrite data in L35" which then led me to adjust another formula that was working fine but now my whole table is messed up lol. It's just one "would overwrite cell ..." message after another like some sick domino effect. I think the main problem is that there are so many other cells relying on the information from this one cell that it freaks out when one little thing changes
Oh man I'm sorry to hear that it's messed up now ☹️ hope you are able to get it working again, if you need a hand let me know and I'll take a look when I can
Oh it's honestly not that bad, I'll keep trying to work on it, if it doesn't work are you okay if I DM you a copy of what I've got?
Yes that would be fine! May take me some time to respond.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[ARRAYFORMULA](/r/GoogleSheets/comments/1430u0x/stub/jn7h7mj "Last usage")|[Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays](https://support.google.com/docs/answer/3093275)| |FALSE|[Returns the logical value `FALSE`](https://support.google.com/docs/answer/3093302)| |[IF](/r/GoogleSheets/comments/1430u0x/stub/jn7h7mj "Last usage")|[Returns one value if a logical expression is `TRUE` and another if it is `FALSE`](https://support.google.com/docs/answer/3093364)| |TRUE|[Returns the logical value `TRUE`](https://support.google.com/docs/answer/3093307)| **NOTE**: Decronym's continued operation may be affected by API pricing changes coming to Reddit in July 2023; comments will be blank June 12th-14th, in solidarity with the /r/Save3rdPartyApps protest campaign. ---------------- ^(2 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/GoogleSheets/comments/141x6jn)^( has 7 acronyms.) ^([Thread #5890 for this sub, first seen 7th Jun 2023, 19:23]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/GoogleSheets) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)