• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

AN =IF Question

S

Snowwy

New Member
Hi Chandoo,

An Awesome site! and loved the Salary survey!!

Here is a challenge>

I have to work out how to increase a fine based on two factors

1.The number of consecutive occurrences of failure over various periods

Example:

Mth1 £10, Mth2 £20, Mth3 £5 etc...

The fines ratchet up the more often they repeat.

EG: 2 Mths x 1, 3 Mths x 2, 4 Mths x 3, 5 Mths x 5, 6 Mths and over x 8

this is fine up to a point but here is the other point

2. The Ratchet resets to 1 every time there is no fine in the period and the whole thing starts again.

Its a kind of moving calculation over any 6 Mth period but I am not sure how to make it move?


Would really welcome some ideas please.


Many Thanks


Snowwy
 
Hi Narayan,

Thanks for asking.

It is for KPI calculations (Key Performance indicators)

For example KPI If we carry out say 100 calls per month and fail 5% we get a fine say £10 based on a lookup table.


That part I have solved.


If we fail the KPI a second time the following Month we still pay £10 but if we fail again in Month 3 the ratchet (multiplier) is applied.

In this case times 2 so the £10 becomes £20.


Again that is no problem.


It increases every occurrence up to 6 Months when it becomes 8 times. (Not suprising)

The problem I have is looking at data over a period when if we don't fail, the ratchet resets back to times 1.

[pre]
Code: 
So the Data would look like this
Month     Fine   Ratchet  Outcome
Month 01 - £10    x1       £10
Month 02 - £10    x1       £10
Month 03 - £10    x2       £20
Month 04 - £10    x3       £30
Month 05 - £0     x1       £0  (Ratchet resets)
Month 06 - £0     x1       £0
Month 07 - £10    x1       £10
Month 08 - £10    x2       £20
Month 09 - £10    x3       £30
Month 10 - £10    x5       £50
Month 11 - £10    x8       £80
Month 12 - £10    x8       £80 (Stays at 8 times)
[/pre]
At any time there is no fine, the ratchet resets.


How do I write in Excel to pick that up automatically?


Hope that helps.


Regards


Snowwy
 
Hi Snowwy ,


Thanks for the information. As I understand it , the data will be just 1 items , in 1 column ; you want against each entry , the outcome in the 4th column , based on the 2 rules you have given earlier.


In column 1 , the month number appears as a piece of entered data ; in column 3 , a formula gives the ratchet value ; you will separately use a lookup table to decide the fine in column 2.


The total fine to be paid , in column 4 , is just a multiplication of the values in column 2 and column 3.


Is this correct ?


If so , then can we conclude the following ?


1. If the fine = 0 , then the ratchet = 1

2. If the fine > 0 , then ratchet is calculated as the ( number of months the fine has been greater than 0 ) - 1


3. If ratchet is greater than 5 , then put it to 8.


Can you confirm the above ?


Narayan
 
Hi Narayan,


Yes the fine is a calculation from another formula.

1. Correct

2. Correct

3. Correct

4. The fine resets to 1 anytime there is no fine in any given month.


Regards


Snowwy
 
You could use a helper row. I made a look up table at K1:L6 with this data:

[pre]
Code: 
0	0
1	10
2	20
3	30
4	50
5	80
[/pre]
Then used this equation starting in C2 to set the fine:


=IF(B2>=5,C1+1,0)


You do have to type in the first fine level in C1, but that didn't seem like a big issue.


The fine value is then pulled into Col D with:


=INDEX($L$1:$L$6,MATCH(C1,$K$1:$K$6,1))
 
Hi Mike,


I have tried it and although the calculation works in terms of value, what ultimately I am looking for is something that picks up the number of occurrences of a fine, not a fine value.


The ratchet works on repetative failures from a deterent point of view, and resets back to 1 if you pass.


Thanks though, I am not quite there yet.


Regards


Snowwy
 
Hi Snowwy ,


Sorry I can't help you further on this today , because it's late here in India. I'm sure others will pitch in.


If you can wait till tomorrow morning , I can certainly help.


Narayan
 
The method I outlined does reset given a error of less than 5 percent and does yield the correct fine level based on your original criteria. However, it sounds like there's an additional requirement that you're looking for.


When you say "number of occurrences of a fine", do you want


1) the number of fines over a given time period


2) the number of times a specific fine has occurred in the data set


3) the number of times a specific fine, or fine within a given range, has occurred over a given time period


4) the largest, or smallest, fine over a given period


5) the largest run of fines over a time period
 
Hi Snowwy ,


I was just going back over your data , and I still have a few doubts :


1. Month 01 and Month 02 are consistent with your initial description ; Month 03 and Month 04 are also OK.


2. Month 05 is OK because the fine was 0 in that month ; Month 06 is another similar month.


3. The fines should start ratcheting up again , so Month 07 starts with 1 , which is fine.


4. Shouldn't Month 08 also be 1 , just as Month 02 was ?


5. Irrespective of Month 08 changing , you have said the fine multiplier should hit the ceiling of 8 from the 6th month onwards ; since the fine became 1 in Month 07 , shouldn't it become 8 only in Month 12 ?


Can you please clarify ?


Narayan
 
Hi Mike and Narayan,


Just got up...


Narayan - You are correct Month 08 should have been 1 just as Month 02 was. My apologies for the error.


Thanks for both your inputs and sorry to confuse you.

Let's see if I can explain myself better.


The mechanism I am trying to 'automate' is the application of the ratchet (or multiplier) to a spreadsheet that captures the result of KPI (Key Performance Indicators) data.

Each one measure different things such as attendance, response etc. and to each KPI there are different tolerances around pass or fail.

All this I have solved and the end result each period (Month) produces a pass or fail based on the data collected.


The ratchet only applies for 'consecutive repeated failures' as an added 'pain' to the fine.

The Ratchet Table is below


Measurement Period Ratchet

1st occurrence 1

2nd 1

3rd 2

4th 3

5th 5

6th and over 8


If at any time there is no failure and hence no fine, the ratchet resets to 1

Then the whole process starts again.


I have solved it manually by the introduction of a 'drop-down' which applies the ratchet based on someone looking down the table for repeated failures and choosing the right ratchet.

But my boss doesn't want that as it could lead to mistakes!


So here I am, looking to you guys?!


This is my first 'posted' question and I love excel and this site and I have learned much from it.


I hope to learn more and really appreciate your time in trying to help.


I look forward to hearing from you and understand the time differences across the world.


Many thanks in advance,


Snowwy
 
Hi, Snowwy!


Give a look at this file:

https://dl.dropbox.com/u/60558749/AN%20%3DIF%20Question%20%28for%20Snowwy%20at%20chandoo.org%29.xlsx


No VBA code, no array formulas, no helper columns, only a single formula in column C. The costs of this easiness are:

a) this not so simple formula

=SI(B11="N";0;BUSCARV(CONTAR.SI(INDIRECTO("B"&MAX(FILA(A$10)+1;FILA()-SI(B11="Y";SI(B10="Y";SI(B9="Y";SI(B8="Y";SI(B7="Y";SI(B6="Y";6;5);4);3);2);1);0)+1-1*(1-SIGNO(SI(B11="Y";SI(B10="Y";SI(B9="Y";SI(B8="Y";SI(B7="Y";SI(B6="Y";6;5);4);3);2);1);0))))&":B"&FILA());"Y");RatchetTable;2;FALSO)) -----> in english: =IF(B11="N",0,VLOOKUP(COUNTIF(INDIRECT("B"&MAX(ROW(A$10)+1,ROW()-IF(B11="Y",IF(B10="Y",IF(B9="Y",IF(B8="Y",IF(B7="Y",IF(B6="Y",6,5),4),3),2),1),0)+1-1*(1-SIGN(IF(B11="Y",IF(B10="Y",IF(B9="Y",IF(B8="Y",IF(B7="Y",IF(B6="Y",6,5),4),3),2),1),0))))&":B"&ROW()),"Y"),RatchetTable,2,FALSE))

b)starting table at row 6 (leaving -5) rows empty for working with moving ranges. I started table at row 10 just for hiding rows 1:9 which have the 2 named ranges used:

FineCell: F2 (title in F1)

RatchetTable: H2:I9 (titles in H1:I1)


Just advise if any issue.


Regards!
 
Hi, Snowwy!


I wrote that the formula was not so simple, but that's not fully true: it as simple as an IF with a VLOOKUP just with an argument a little tricky.


The IF + VLOOKUP:

=IF(B11="N",0,VLOOKUP(<how_many_straight_Y_are_in_the_last_6_rows>,RatchetTable,2,FALSE))


The <how_many_straight_Y_are_in_the_last_6_rows>:

COUNTIF(<last_6_rows>,"Y")


The <last_6_rows>:

INDIRECT("B"&MAX(ROW(A$10)+1,ROW()-<number_of_straight_Y>&":B"&ROW())


The <number_of_straight_Y>:

<this_first_count>+1-1*(1-SIGN(<this_first_count>))

Correction from "-1*(1-SIGN(xxx))" needed for 0 value, so as to not return an offset negative range.


This <this_first_count>:

IF(B11="Y",IF(B10="Y",IF(B9="Y",IF(B8="Y",IF(B7="Y",IF(B6="Y",6,5),4),3),2),1),0)


Et voilá! C'est tout!


Regards!
 
Hi SirJB7,


I have just started to look at your spreadsheet and understand some of it :-{


I am a bit slow in 'unpicking' so that I can then translate into my project.


I see you use a Y N function which I hadn't thought of as I was using an

IF( cell value >0...) to be the trigger.


That is, if there was a value in the cell for the fine?


Would it still work with that approach?


Regards!
 
Hi, Snowwy!


As I didn't know how you defined the fail, I used a Y/N test. You should replace:

a) B11="N" by XXnn=0 (being XX cell column and nn cell row)

b) COUNTIF(xxx,"Y") by COUNTIF(xxx,">0")

c) B11/B10...B6="Y" by B11/B10...B6>0


If you happen to get stuck consider uploading a sample file, you may find uploading guidelines within second green sticky post at this forums main page.


Regards!


PS: I'm leaving now and will be coming back in a few hours.
 
Hi SirJB7,


Sorry I haven't got back to you, I have been running around the Country on other things.


I think your answer is BRILLIANT although I don't fully understand how the INDIRECT function works. :-(


In my spreadsheet though it works using the Months across the sheet and so I have been trying to transpose your sample from vertical to horizontal to no avail...


It's probably because I don't entirely understand how it all works.


How would I change the formulae to make it pick up months going across the sheet rather than down.


Sorry if I seem a bit dim!


Kind Regards


Snowwy.
 
SirJB7,

Just checked the solution !!! whew !!!.. I spent 20 minutes to understand the formula..

Understood.. But dont know if I will be able to replicate the same...

Because understanding the formula is just 1 part.... Applying it is the game...

Thanks
 
A different approach that's a little shorter in length...

Assuming Named Range of RatchetTable that looks like this:

[pre]
Code: 
0	0
1	1
2	1
3	2
4	3
5	5
6	8
[/pre]
Array formula to calculate Ratchet value is:

=LOOKUP(ROW()-MAX(ROW(B$2)-1,ROW($2:2)*(B$2:B2=0)),RatchetTable)


Just remember to confirm formula using Ctrl+Shift+Enter. Layout assumes that the Fine column is col B, and that the formula is starting in row 2. If not, change all the numbers (all the 2's) accordingly. Another advantage of this formula is that it doesn't require extra rows above the starting formula.


Method:

As the real question is how long has it been since the ratchet reset, we just need to know when was the last time there was no fine. That's where the MAX function helps out. The first bit of ROW(B$2)-1 is there to tell the formula where the header row would be (or no header row). The latter part uses an array multiplication to give the row numbers where there is no fine. Once we know the last row where there was no fine, we subtract that from the row we're currently on. This lets us know the count of how many months there have been. Throw that count into a LOOKUP function, and we're done.
 
Hi Luke,


Wow another version to get my head around,


many thanks, I shall report in due course.


Snowwy
 
Hi, Snowwy!


The INDIRECT function gives you a reference to a cell or range, but instead of writing it directly as B4, you build it as an expression like '"B" & 12/3' (unquoted simple) and encapsulate it within the function.


So:

=B4

it's the same as:

=INDIRECT("B" & 12/3)


About your question, do you say that you have the months transposed across columns and not down rows?


Regards!


PS: BTW, thank you for you very kind words :)
 
@majay1973

Hi!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


And about your question...


Just ask any doubt, if it's about the same example as this topic; otherwise you should start a new post.


Regards!
 
Hi, Snowwy!


Please download the updated file from same previous link. In same sheet Hoja1, light orange area is the monthly rowed version and light blue area is the monthly columned version.


I simply did this:

a) copied transposed the 4 columns

b) changed the ROW() by COLUMN() functions

c) changed the starting cells from A$10 to $E10 (note the change from fixing row to fixing column)

d) changed built method for the INDIRECT function from:

"B"&xxx&":"&"B"&yyy

to:

ADDRESS(xxx,2,4,1)&":"&ADDRESS(yyy,2,4,1)

(4 is for relative addressing, and 1 for A1 format), so as to make transparent the row/column switch ("B" didn't worked for row construction)

e) changed the previous 2 for row-ed to COLUMN()-1 and the equivalent 11 for column-ed to ROW()-1


Just advise if any issue.


Regards!


PS: I know realize that you hadn't a Y/N condition but a >0 test value. Well, I hope (I'm sure :)... may I?) that you can manage to handle the same modifications as described in my first post about it. If not, let me know.
 
Hi SirJB7,


Many thanks for the second version of the upload, I did try transposing the Row to Columns myself but as I didn't understand the INDIRECT function, I couldn't make it work.

You have now cleared that up for me.


I have a fair amount of work to to adapt my worksheet as in its current form it is laid out with multiple KPI's down each sheet and lots of gaps between columns to make it look pretty but not helpful when it comes to replicating formula.


Each sheet spans 20 years too!


I am away for a week after today so I will see how much I can get done beforehand.


In the mean time, thanks for all your hard work, I have certainly learned a lot more than I expected.


Perhaps I should do a course!!


Kind Regards until next time


Snowwy
 
You must log in or register to reply here.
Back
Top