Add +1 for last 5 years’ sales #s

deirdreob

New Member
Joined
Sep 15, 2013
Messages
12
I'm getting all twisted up in my logic for the following situation, or maybe this can't be done with a simple formula.... Thank you!
In B3, add +1 for each year up to 5 that company has had sales and they have not declined; also add +1 for each year company’s sales have increased.

A B C D E F G H I
1 YEAR 2013 2012 2011 2010 2009 2008 2007
2 SALES 500 250 100 0 100 50 20
3 SCORE __
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
It probably can be done with a formula, but the logic is not completely clear to me.

Can you tell us what the "SCORE" should be for that example ?
 

deirdreob

New Member
Joined
Sep 15, 2013
Messages
12
It probably can be done with a formula, but the logic is not completely clear to me.

Can you tell us what the "SCORE" should be for that example ?
====================
Thnx Gerald, "Score" is the sum of the points.
In parsing the Rqmts into my Logic, have I captured what is needed (are the two underlined statements the same) ... or am I too tired?
Rqmt: "add +1 point for each year up to 5 that the company has had sales and they have not declined"
--- Logic: ... company's current sales are greater than zero (or <> 0), and not less than the previous year
Rqmt: "add +1 point for each year up to 5 that company’s sales have increased"
--- Logic: ... company's sales are are greater than the previous year
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
OK, so what should the actual numbers be ?

Are the underlined statements the same ? It seems to me that they ARE effectively the same.
But to be certain, what I would try and do is check this with the person who set these requirements.
Maybe they meant them to be different, but phrased them slightly differently from what they meant.
 

deirdreob

New Member
Joined
Sep 15, 2013
Messages
12
G - Not possible to clarify the Rqmts. The sales are figures are (from most recent backward; displayed in a row): 500 250 100 0 100 50 20 ....
Thanks! D
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Not possible to clarify the Rqmts.
Really ? Then you're going to have to guess. I've given you my guess :)

The sales are figures are (from most recent backward; displayed in a row): 500 250 100 0 100 50 20 ....
Thanks! D
Sorry, I think you misunderstand me.
What I mean is, what do YOU think the answer should be in this case ?
 

deirdreob

New Member
Joined
Sep 15, 2013
Messages
12
Why 8 exactly ?

It looks to me like it might be 10.
---------------
I have contacted the author for a rqmts clarification.
You're right; it's 10: To evaluate 5 years of data, the periods from 2013 - 2006 must be used, therefore 6 comparisons are made, of which only 1 did not meet all of the rqmts.

Sales values for 2013 - 2008 span B2 - G2.
The copied formula =IF(AND(B2<>0,B2>C2),2,0)
and displayed Scores span B3 - G3.
I sum the Scores in B4.
The second to last argument of the formula displays a 2 when both conditions are true - as the two "increasing sales" rqmts appear to be the same. (Hopefully author will generously clarify this.)

Nesting If(and statements is not one of my strong points, but it would be preferable to summing the individuals
Thanks for hanging in there! Stay tuned for an update, D
 

Watch MrExcel Video

Forum statistics

Threads
1,099,150
Messages
5,466,970
Members
406,513
Latest member
t0ny84

This Week's Hot Topics

Top