?
Solved

Increase/Decrease formula

Posted on 2014-02-24
4
Medium Priority
?
309 Views
Last Modified: 2014-02-26
Hi All,

Attached please find the sheet attached.

In sheet Fidler1 Column A is just random number of 1 and 0.   (You will have to redo the random number between 1 and 0 as I have done a copy paste values)

I am trying to come up with a formula that will handle Fidler2, Column G's issue of trying to increase by 1.25 if the previous value was positive or decrease by .1 (multiply by .9) if the previous value was negative.  Column "C" denotes whether the number was up or down represented by a "1" or "-1".

Column "I" is a manual representation of how this would work if it were coded in vba or with excel formula in the best case scenario.

Also, if the value becomes greater than 1000 I would like to reset it to 125.
If the value becomes less than 25 I would like to reset it to 50

Any ideas how this might look? thanks!
Fiddler.xlsx
0
Comment
Question by:BostonBob
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 23

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 1000 total points
ID: 39887049
As I see it you calculate the value based on the previous value, when column G (Sheet2) is 125 or -125.

A formula like this can do it, but will take some time for 10000 rows.
Sheet2 J2 copied down.
=IF(ABS(G2)=125,IF(ISERROR(LOOKUP(2,1/(ABS($G$1:G1)=125),($J$1:J1))),125*IF(G2<0,0.9,1.25),IF(LOOKUP(2,1/(ABS($G$1:G1)=125),($J$1:J1))*IF(G2<0,0.9,1.25)>1000,125,IF(LOOKUP(2,1/(ABS($G$1:G1)=125),($J$1:J1))*IF(G2<0,0.9,1.25)<25,50,LOOKUP(2,1/(ABS($G$1:G1)=125),($J$1:J1))*IF(G2<0,0.9,1.25)))),"")

Open in new window

Another approach.
With autofilter the values 125 and -125 in column G, are extracted and placed in column N.
The formula in M2 to get the result is much simpler and much more efficient, since the reference is only to cells left and above the formula.
=IF(ISTEXT(O1),125*IF(N2<0,0.9,1.25),IF(O1*IF(N2<0,0.9,1.25)>1000,125,IF(O1*IF(N2<0,0.9,1.25)<25,50,O1*IF(N2<0,0.9,1.25))))

Open in new window


Have reduced the number of rows with data to 500 on both sheets to reduce file size.
Fiddler-500-rows.xlsx
0
 
LVL 14

Accepted Solution

by:
Faustulus earned 1000 total points
ID: 39887089
I'm not sure I fully understand your requirement. Therefore, here is what you might need to finish the job by yourself. All references to row 1 can be copied to any other row.

The initial requirement is to sort C1 into -1, 0, and 1. I suggest to do that by differentiating between zero and non-zero:-
IF(C1,TRUE,D1)
So, if C1 = 0 the result will leave D1 unchanged. I presume that D1 is what you referred to as "the previous value".
If C1 is a number (1 or -1) the above formula will return TRUE, and this result must be replaced with something else, like,
IF(C1<0,D1*-0.9,D1+1.25)
I have doubt on the multiplication by -.9 because I don't know how D1 might be negative, but in your example you have a multiplication by -1. Anyway, I think you fully understand this formula and can modify it to suit your needs. In the following this formula replaces the TRUE result of the first formula:-
=IF(C1,IF(C1<0,D1*-0.9,D1+1.25),D1)

Open in new window

=IF(C1,IF(C1<0,D1*-0.9,D1+1.25),D1)
Now, for your next request you need a formula like this:-
=IF( [Result] >1000, 125, IF( [Result] <25, 50, [Result] ))
There are two ways of handling this. Take your pick.
1. You can replace each [Result] in the formula with the entire formula developed above (without the equal signs).
2. I recommend to place the result formula in a helper column (I would probably hide it) and refer to that result. If your result is in column G then the new, adjusted result formula would refer to G1, like,
=IF(G1 >1000, 125, IF(G1<25, 50, G1))

Open in new window

=IF(G1 >1000, 125, IF(G1<25, 50, G1))
0
 

Author Comment

by:BostonBob
ID: 39887689
Thanks every one.  Give me a day to look at it all.
0
 

Author Closing Comment

by:BostonBob
ID: 39888491
These formulas that you guys come up with would stump space aliens I bet! Thanks again.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question