LinkBack URL
About LinkBacks
This is a part of a (LONG) Function
Is there way to Shorten this?
Please Login or Register to view this content.
This Function (formula) is then copied all the way down
TiA
Ps: Please a hint first instead of the "pre-cooked" solution
This is a part of a (LONG) Function
Is there way to Shorten this?Please Login or Register to view this content.
This Function (formula) is then copied all the way down
TiA
Ps: Please a hint first instead of the "pre-cooked" solution
Last edited by iammike; 04-15-2024 at 09:37 PM.
Hint: take a look a the SUM function. The 2's can be factored out....
Ben Van Johnson
Thx!
Would that be in a Array Formula?
Edit: I do believe it's not, as there is NO criteria. Or is there?
Last edited by iammike; 04-15-2024 at 10:01 PM.
No it's not.
Oke breaking it down
I can do a sum of the ranges
1) L8+M8+N8+O8+P8 - (L8:P8)
2) do 2*(Q8:S8)
But then I am still stuck with
+H8
and (2*I8)
Would it then be that much shorter?
This is what I got thus far
Edit:Please Login or Register to view this content.
I think I got it
Please Login or Register to view this content.
Last edited by iammike; 04-15-2024 at 10:26 PM.
Combine SUM(Q8:S8)*2+(2*I8) into one SUM. SUM arguments can be combinations of single cells and ranges separated by commas.
I could see turning that into a single SUMPRODUCT() function. (as a hint, let me know when you want to see how I might put it together).
Originally Posted by shg
@protonleah.
Thx, If you see my edit in my previous post, I think I did just that
@MrShortyPlease Login or Register to view this content.
Not yet, I want to try it myself, I learn way more by trying it myself at first.
Last edited by iammike; 04-16-2024 at 12:47 AM.
Try
Formula:
Please Login or Register to view this content.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
You can get rid of some of your brackets (in pairs)
Pete
@Pete_UK,
I have honesty NO idea how to shorten that even further (Oke can remove some brackets
I have been trying with SUMPRODUCT, but not much luck.
@avk
Is the Solution
(with some brackets removedPlease Login or Register to view this content.
Not better, because I combinein there asPlease Login or Register to view this content.Please Login or Register to view this content.
Last edited by iammike; 04-16-2024 at 05:00 AM.
Is this any shorter?
=SUM(H8:I8;L8:S8;I8;Q8:S8)
Only one function call, as well.
If J8 and K8 are both numbers, you could do this:
=SUM(H8:S8;I8;Q8:S8)-J8-K8
Hope this helps.
Pete
=SUM(H8:I8;L8:S8;I8;Q8:S8)
Works great. Brilliant this, very clever to add for example I8 again (never thought of that but it's totally logical)
Glad to help, and thanks for the rep.
Pete
@Pete_UK
But may I say, I find
=SUM(H8;L8:P8)+(SUM(I8;Q8:S8)*2)
Easier to read (but that is because I am not that experienced yet
Yeah, that's fine, but you don't need the brackets around the second SUM function:
=SUM(H8;L8:P8)+SUM(I8;Q8:S8)*2
Hope this helps.
Pete
It most certainly doesHope this helps.
I was thinking of something likeI have been trying with SUMPRODUCT, but not much luck.
=SUMPRODUCT(H8:S8,{1,2,0,0,1,1,1,1,1,2,2,2}) or
=SUMPRODUCT(H8:S8,H$7:S$7) where H7:S7 contains the array of multipliers.
I usually prefer the latter version with the helper cells, because then it is easier to edit the array of multipliers, if that is ever necessary.
Here the WB where the Formula is used.
It's for our upcoming Camper Trip, I want to exactly know if the prices the Camper Pitches charge us are correct.
Last edited by iammike; 04-16-2024 at 10:26 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks