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

I need help with a new formula

D

Draoul2007

New Member
Hello All,

I have a formula that I am using with a google spreadsheet. I know this is for excel, but every time I have asked for the formula has worked. Here is my logic: I would like a formula to score a result as a 10 if there is a 10% improvement. It currently scores a 9 if there is a 3% improvement.

Here is the formula for the mile:
=IF(ISBLANK(M2),"No Data",IF(M2<=IF(B2="M",TIME(0,8,0),TIME(0,9,0)),10,IF(M2<=IF(B2="M",TIME(0,10,30),TIME(0,11,30)),IF(M2<=L2*(1-0.03),9,8),IF(M2<=L2*(1-0.1),7,IF(M2>0,6,"Error")))))

Here is the formula for the Pacer. (The pacer is an activity in my class that test cardiovascular endurance) Here is the formula:
IF(ISBLANK(P2),"No Data",IF(P2>=IF(B2="M",96,85),10,IF(P2>=IF(B2="M",62,52),IF(P2>=O2*(1+0.03),9,8),IF(P2>=O2*(1+0.1),7,IF(P2>0,6,"Error")))))

Your help would greatly appreciated.
 
Hi ,

Rather than try to debug your formula , can you explain in simple English what you want the formula to do ?

Your first formula involves the cells B2 , L2 and M2 ; B2 is a character , while L2 and M2 are numeric.

List out the possibilities for B2 , L2 and M2 , and explain what the output should be for each combination of the three cells.

Do the same for the second formula , which involves the cells B2 , O2 and P2.

Doing it this way will not only ensure you get in a shorter time a formula which works , but will also ensure that we have covered all cases , something which the formula you have posted might not do.

Narayan
 
Hello Narayan,

Thank you for your quick response. I have a spreadsheet that I use during class to assign points. There are 7 fitness tests that I give. I use a v-lookup with part of the spreadsheet. This parts works well. The part I need help with is the mile and the pacer. Those were the formulas I shared with you. Basically, the formula works great but I would like to add something new to it. I would like to have the formula be able to give a score of 10 if the student improves by 10%.
Here is the original formula:
=IF(ISBLANK(M2),"No Data",IF(M2<=IF(B2="M",TIME(0,8,0),TIME(0,9,0)),10,IF(M2<=IF(B2="M",TIME(0,10,30),TIME(0,11,30)),IF(M2<=L2*(1-0.03),9,8),IF(M2<=L2*(1-0.1),7,IF(M2>0,6,"Error")))))

I would like to add:
IF(M2<=L2*(1-0.1),10

Here is the link to my spreadsheet
 
Hi ,

The link to your workbook is not available.

The formula you have posted is :

=IF(ISBLANK(M2),"No Data",IF(M2<=IF(B2="M",TIME(0,8,0),TIME(0,9,0)),10,IF(M2<=IF(B2="M",TIME(0,10,30),TIME(0,11,30)),IF(M2<=L2*(1-0.03),9,8),IF(M2<=L2*(1-0.1),7,IF(M2>0,6,"Error")))))

The first part is testing for whether M2 is blank , and if so , it displays No Data in the cell where this formula is entered. We can safely eliminate this for purposes of understanding the remaining portion of the formula , which will now reduce to :

=IF(M2<=IF(B2="M",TIME(0,8,0),TIME(0,9,0)),10,IF(M2<=IF(B2="M",TIME(0,10,30),TIME(0,11,30)),IF(M2<=L2*(1-0.03),9,8),IF(M2<=L2*(1-0.1),7,IF(M2>0,6,"Error"))))

There are two sections which are identical as far as the logic testing is concerned , with two different sets of time values ; I have colored these.

I am not able to understand either the purpose of these sections or how they can work correctly.

Can you please upload your workbook ?

Narayan
 
Hi ,

Thanks for uploading your file , but the logic is still not clear.

Your existing formula already has the logic that you seem to be asking for.

Let us first detail the logic. Considering only males , the logic for the following scores is :

10 - if the value in column M is less than or equal to 8 minutes

9 - if the value in column M is less than or equal to 10 minutes 30 seconds , and if the improvement in timings is greater than or equal to 30 %

8 -

7 - if the value in column M is less than or equal to 10 minutes 30 seconds , and if the improvement in timings is greater than or equal to 10 %

6 - if the value in column M is greater than zero

Else display Error.

Can you clarify / confirm this logic ?

Once this is done , we can move on to females , with the change that the absolute time threshold values in the various cases would be 9 minutes for females instead of 8 minutes , and 11 minutes and 30 seconds for females instead of 10 minutes and 30 seconds.

Narayan
 
Hello Narayan,

I would like the formula to include giving a score of 10 if a student improves by 10% in the healthy fitness zone (8:00-10:30). I know a student gets 10 point if they do better than 8:00 minutes.

This formula will work but does not recognize the difference between a male and a female.
=IF(ISBLANK(K2),"Z",IF(K2<=TIME(0,8,0),10,IF(K2<=TIME(0,10,25),IF(K2<=G2*(1-0.1),10,IF(K2<=TIME(0,10,30),IF(K2<=G2*(1-0.03),9,8),IF(K2>TIME(10,30),IF(K2<=G2*(1-0.1),7,6,"Error")))))))

The formula I use will recognize the difference between a male a female but not the 10% improvement in the healthy fitness zone (8:00-10:30).
 
Hi ,

I can only repeat what I said before ; let us fix the logic first , and the formula will result easily and immediately.

I had mentioned the logic as I understood it , and had mentioned some gaps in the logic ; those are still to be clarified.

I had also mentioned that if a fool-proof formula can be developed for males , extending it to females is not time-consuming or difficult.

The formula you have posted in your latest post is as follows :

10 - if the time in column K is less than 8 minutes OR if the absolute timing is less than or equal to 10:25 , and there is at least a 10 % improvement in the timing compared to the benchmark in column G.

9 - if the time in column K is less than or equal to 10:30 , and there is at least a 30 % improvement in the timing compared to the benchmark in column G.

8 - if the time in column K is less than or equal to 10:30 , and there is a less than 30 % improvement in the timing compared to the benchmark in column G.

7 - if the time in column K is greater than 10:30 , and there is at least a 10 % improvement in the timing compared to the benchmark in column G.

6 - if the time in column K is greater than 10:30 , and there is a less than 10 % improvement in the timing compared to the benchmark in column G.

Can you clarify or confirm this logic ? If you confirm this logic , then the formula you have posted already does this.

Once you clarify / confirm this logic , we can extend the formula to cover females.

Narayan
 
Hello Narayan,

10- Correct
9-Should be a 3% improvement.
8-Should be within the 8:00-10:30 but less than a 3% improvement
7-Correct
6-Correct

The formula below will do everything except figure out the 9 points.
=IF(ISBLANK(M2),"No Data",IF(M2<=IF(B2="M",TIME(0,8,0),TIME(0,9,0)),10,IF(M2<=IF(B2="M",TIME(0,10,30),TIME(0,11,30)),IF(M2<=L2*(1-0.1),10,8),IF(M2<=L2*(1-0.1),7,IF(M2>0,6,"Error")))))

When I try and insert this IF(M2<=L2*(1-0.03),9), I get a FALSE statement.

Thank you for your help.
 
Hi ,

See if this formula is correct ; I have used 3 helper columns and a times table for ease of understanding ; if everything is correct , and you want to do away with the helper columns and the table , that can be done easily.

Narayan
 

Attachments

  • Sample File.xlsx
    78.4 KB · Views: 8
Hello Narayan,

I looked at the formula. The L column is the bench score. The M column is the current score. The formula should have M column referencing the L column. For example, in row 3 the base mile time was 9:15, the current score was 10:00. Your formula gave a score of 10. The formula I used would give it a score of 8.
 
Hi ,

Please look at the file I uploaded , and the formulae that have been used in the helper columns AC , AD and AE.

I am afraid I have helped or tried to help as much as I could.

In case you don't find it helpful , I am sure there are others who will help.

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