# Best Of Excel Forum

1. ## Best Of Excel Forum

Hi All
I compiling a best of the excel forums function combination solutions to attach to the forum
does anyone remember who need created this solution? I can find it any where

If you have any favorites please send them in .They will be credited to the author and added to the workbook. they can be one liners like

=IF(OR(ISNUMBER(SEARCH({"FOR","RPA","RGA"},B3))),RIGHT(B3,4),IF(OR(ISNUMBER(SEARCH({"LS","JU","SS"},B3))),LEFT(B3,MIN(FIND("^^",SUBSTITUTE(B3,{"LS","JU","SS"},"^^")&"^^"))-1),"")) from NBVC

2. ## Re: Best Of Excel Forum

Here.. all posts where DonkeyOte posted

http://www.excelforum.com/search.php?searchid=2018879

4. ## Re: Best Of Excel Forum

another two may be
=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",INDEX(\$A2:\$F2,MATCH(1,INDEX((\$A2:\$F2<>"")*ISNA(MATCH(\$A2:\$F2,\$J2:J2,0)),0),0))))
=IF(COLUMN(K2:K2)<=SUM(--(\$A\$2:\$J\$2<>"")),INDEX(\$A\$2:\$J\$2,0,SMALL(IF(\$A\$2:\$J\$2<>"",COLUMN(\$A\$2:\$J\$2),""),COLUMN(K2:K2))),"")

5. ## Re: Best Of Excel Forum

I'm always reluctant to asign authorship to any solution, no matter how brilliant.
Any given solution, while new to any particular forum, usually appeared someplace else,
or was built upon some other related solution. Consequently, you'll never see me
referring to "my formula" or "my solution". Rather, I refer to "the formula I posted". I'm happy
to mention that I first saw a solution posted by [enter somebody's name here].

6. ## Re: Best Of Excel Forum

Point taken
Do you have any good threads that come to mind?

7. ## Re: Best Of Excel Forum

I dont realy think that any one thread or solution can be pin pointed as better that another.

there are many talanted guys and girls on here that are amazing in their own right, some have a great overall understanding, some chart like no other, and others post code that 200 nasa monkeys go bannanas over.

8. ## Re: Best Of Excel Forum

Certianly are, thats undisputed. Just wanted to compile a best of soultions, no names as per Rons point, just examples from the forum that are your favorites. Like mine the fuel usage solution.
or this one

and another

and

good to know

12. ## Re: Best Of Excel Forum

How about combining all this in excel workbook or word

I have some of them stored that way but on Croatian...

13. ## Re: Best Of Excel Forum

hey zbor
yep send them in, when I get about twelve I"ll post the workbook

another

SumIf <>

16. ## Re: Best Of Excel Forum

It does contain offset but i like it

17. ## Re: Best Of Excel Forum

Good old sumproduct

18. ## Re: Best Of Excel Forum

Pike, I don't get it. The formula is hard to understand if your screenshot does not include the column letters and row numbers. Also, it might help to present a few words about the problem, like "sum all values with letter B for John on Friday"

But whichever way I apply column headers, the formula does not do it. In order to test it, I'd have to re-type the whole setup and make assumptions about cell references. A file attachment instead of a screenshot might be a better way to go.

19. ## Re: Best Of Excel Forum

Hello Pike,

In my opinion if you're going to do this sort of thing as a reference guide then you probably ought to follow some sort of "best practice". I know that's subjective to a degree but there are certain things that I think you need to stick to

For example with regards to the last SUMPRODUCT formula.....

The formula displayed sums for Tuesday rather than Friday and there's no need for IF functions like that in SUMPRODUCT you can write it like this and avoid CSE (and sum Friday)

=SUMPRODUCT(E4:E17,J4:J17,(D4:D17=D21)*(C4:C17=D20))

I would have thought, however, that the intent here would be for the user to specify any name, letter and day and get the result automatically so to get the correct column to use based on the day in D19 that would become

=SUMPRODUCT(E4:E17,INDEX(F4:J17,0,MATCH(D19,F3:J3,0)),(D4:D17=D21)*(C4:C17=D20))

20. ## Re: Best Of Excel Forum

draft for critque

21. ## Re: Best Of Excel Forum

Should we retitle this thread Pike's Peak?

22. ## Re: Best Of Excel Forum

Hi shg
Yes, I've peaked.

all examples attached in the workbook

Excel Forum Workbook 1

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1