5 Criterias in 1 Formula

Hi guys,

Don't know if its even fair to be asking help on this one, as its a formula to possibly challenge even strongest Excel expert... Surely not something I can crack up on my own... in any case, giving it a try, but would perfectly understand if this one is beyond our limits.

In a nutshell, there is a need to come up with ONE single formula for 5 different criteria (which are basically 5 different formulas to fit in 1) If possible, without helper columns/rows, arrays, macros etc.

Additional details are that this formula is to be used as a part of 0 to 10 rating system, where one final number (0 to 10) needs to return average based on 5 different criterias. Again, if possible, without additional columns/rows etc (file already contains lots of data, so if any additional stuff could be avoided, that would be best)

There is no need to try changing how sheets, or data etc are structured (can't change it in original copies anyway), but literally all is required is to just write a single formula which will have all 5 criterias/formulas blended in it.

Attaching actual Excel file with full details, and a screenshot below.

If possible to take a look, any feedback, questions or suggestions are welcome. Thanking (lots) in advance.

https://www.excelforum.com/attachmen...1&d=1509199831 [EXCEL]

2017-10-28_1655.png

Criteria for Criterion 4 needs reviewing as you refer to cells G3,J3 and J3 on Sheet1 but data is in ROW 15 ????

YES, to

@Today, 09:36 AM #8 JohnTopley !

I moved the Criteria 4 box in the end, to make Sheet1 look more neater, and missed on updating already typed in details in Sheet2!

Sorry, yes, referring to Sheet1 E15:H15 here...

Looking at formula in post #10 now, thanks AliGW! Got 8.2 returned, where based on my manual calculations it should have been 8.4... so now checking out the details...

@ Glenn Kennedy - since its a number to be generated only for April, we only look into Jan (year start) + Apr (current) + Mar (previous) Same formula will be used for following/previous months

Diving into studying given suggestions further...

PS: wondering on "I have a funny feeling about how this thread is likely to pan out, though ..." or how it could pan out? :-)

Ok, so I explored the formula suggested by AliGW, and it seems to be a perfect one! It should return correct values too, based on what I see is entered there. Yet...

WHY do I get a final numer 8.2, while it should be 8.4? Just cant get my head around it...

Here is manual calculation:

Criteria #1 has 3 streams = 4 pts
Criteria #2 says 'YES" = 10 pts
Criteria #3 says 4.5 = 8 pts
Criteria #4, Apr balance is higher than Jan AND higher than Mar = 10 pts
Criteria #5 says "YES" = 10 pts

Totals: 4 + 10 + 8 + 10 + 10 = 42

42 / 5 = 8.4

So how is it 8.2 that is returned by, what it looks like, a perfect formula?? Just doesn't make any sense...

Do you also get 8.2 as a final number, AliGW?

I have just claimed 8.4 pts by adding another Income Stream in Criteria #1, but this (after adding another stream of income) now should have been displaying 8.8...

Ah yeah! That's a buggar...

You're right, AliGW, the formula for Criteria #1 needs to be different... The:

MIN(((COUNTIF(Sheet1!C4:C7,">0")+(COUNTIF(Sheet1!C10:C13,">0")+(COUNTIF(Sheet1!C16:C19,">0"))

was there to only share as an example what is used for counting Streams of Sources, and why same format needs to remain (as per comment in yellow bow, Excel file) BUT this formula does not give points as specified in Criteria #1, Sheet2!...

So to finalise the final one, there seems this first formula would be the only one to be updated... Truthfully, no ideas how? So it makes a COUNT only from Cells specified (not from entire column) AND it returns points, as per guides in Sheet2! ?

I dont understand the 'how' but generally all it should say:

IF

MIN(((COUNTIF(Sheet1!C4:C7,">0")+(COUNTIF(Sheet1!C10:C13,">0")+(COUNTIF(Sheet1!C16:C19,">0"))

= 0, then count 0 pts
= 1, then count 1 pts
= 2 then count 2 pts
= 3 then count 4 pts
= 4 then count 6 pts
= 5 then count 8 pts
>5 then count 10pts

And then the points returned from this formula would just blend into points returned by formulas for other criterias, to then divide all returned by 5 = 8.4 (as per initially entered data)

MIN is there for a reason, I think would take long to explain... but needs to stay. Do you want me to go into details?

Ok!

Basically original formula was:

=((COUNTIF(C4:C7,">0")+(COUNTIF(C10:C13,">0")+(COUNTIF(C16:C19,">0")/F4)*10))/F4)*10

The problem with it, however, was that should CountedIF total values are greater than value entered in F4, it returns a number greater than 10. This is not allowed in 0 to 10 rating system, and max number returned should never exceed 10.

So a suggestion was made to use MIN, as this way MIN would pick up a lower number and always show a lower number. Bearing in mind that number entered in F4 would never be greater than 10, this version worked well. Until I bumped into a current situation...

Give me a moment, I might gonna be able to dig out original file, explaining full details on this initial issue...

Ah, here it is!

https://www.excelforum.com/attachmen...1&d=1509205996

It could be that I was using it incorrectly! Would not be surprised :-)

Tried fitting in your above suggested:

LOOKUP(SUM(COUNTIF(Sheet1!C4:C7,">"&0),(COUNTIF(Sheet1!C10:C13,">"&0),(COUNTIF(Sheet1!C16:C19,">"&0)),{0,1,2,3,4,5,6},{0,1,2,4,6,8,10})


into...

=IFERROR((MIN(((COUNTIF(Sheet1!C4:C7,">0")+(COUNTIF(Sheet1!C10:C13,">0")+(COUNTIF(Sheet1!C16:C19,">0"))))))+IF(Sheet1!E5="-",0,IF(Sheet1!E5="YES",10,IF(Sheet1!E5="NO",0)))+LOOKUP(Sheet1!E10,{0,0.51,1.01,2.01,3.01,4.01,5.01},{0,1,2,4,6,8,10})+IF(Sheet1!H15 < Sheet1!E15,0,IF(AND(Sheet1!H15 > Sheet1!E15,Sheet1!H15 > Sheet1!G15),10,5))+IF(Sheet1!E20="-",0,IF(Sheet1!E20="YES",10,IF(Sheet1!E20="NO",0))))/5,0)

I'm sure its brackets somewhere, but taking time keep trying and trying, with same error messages popping up... How exactly does it fit in there, please?

Hmm. I get error 'There is a problem with this formula' ?

Ok! Give me a moment to get all details organised

So, what we want for Criteria #1 is:

1. Look in Sheet1! C4:C7, C10:C13, C16:C19 and count how many times there are balances entered there

(this is needed to find out how many Streams of Income occurred during that particular month)

2. Return points based on number of counts, where IF:

= 0 streams, then count 0 pts
= 1 streams, then count 1 pts
= 2 streams, then count 2 pts
= 3 streams, then count 4 pts
= 4 streams, then count 6 pts
= 5 streams, then count 8 pts
>5 streams, then count 10pts

(this is needed to find out how stable is your current financial situation. 0 pts = Poor, 10 pts = Excellent)

These would be the only 2 conditions.

I'm guessing what's throwing us around is that MIN thing... which is no longer relative for this formula purposes. I kept that original formula in Sheet2! J3 as an example, just be sure that we only count balances from specified cells (C4:C7, C10:C13, C16:C19) and not from entire column.

Blind me... after all... was sweating on this one!

AliGW, guys... I need to let you know, you've been tremendous help to me, and surely to many others. Even though you might not be looking for material rewards from a help that you're giving us, I keep wishing that I would be in a position to give something back in return, for everything you do. If there is anything, let me know?

Little more than a year I didn't even know how to add charts into Excel. Currently working my butt (we can say, from a garage) on building a complete life management system, starting in Excel. I don't know where would I be, if not because of you... I do believe (in fact, I know) that one day all the hard work that is currently being put into this venture, will be rewarded. I hope you won't mind me reaching out back to each of you, when that time comes, and sharing what we managed to build, together.

In a mean time, I'm thanking you, from my heart, for all of your help. Makes me emotional just by trying to explain in words, how happy I am to be a part of this community, and grateful for... you. And everything you do. To me, us, and to the world.

THANK YOU.

AliGW, and everyone else who looked into this one as well - thank you...

Yes, solved... thank you :-)

Hey Marvin, thanks for looking into it.

I'm sure that formulas could be improved, only at this moment of time not looking for perfection, rather just to get working. Task -> formula -> correct results = happy... there is all what's needed, at this stage :-)