Add +1 for last 5 years’ sales #s

deirdreob

New Member
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 __

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Gerald Higgins

Well-known Member
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
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
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

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
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

Really ? Then you're going to have to guess. I've given you my guess

Sorry, I think you misunderstand me.
What I mean is, what do YOU think the answer should be in this case ?

8

Gerald Higgins

Well-known Member
Why 8 exactly ?

It looks to me like it might be 10.

deirdreob

New Member
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

Gerald Higgins

Well-known Member
OK great. I'm not clear, do you still need help on this one ?

Replies
2
Views
386
Replies
3
Views
134
Replies
0
Views
139
Replies
1
Views
369
Replies
1
Views
1K

1,132,895
Messages
5,655,837
Members
418,246
Latest member
VerticalChris

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back