Help with formula

[desert rat]

Hi All,

I am working on some data that is set out on a weekly basis and calculates averages in some of the columns. Instead of retyping the formulas each week I was hoping to copy the formula but change the cells it references without manually typing them for the every week. EG: cell F6 is an average of E5/D5 but when I copy it to N5 I would like it to work out the average calculating M5/L5 and so on for the rest of the weeks of the year.

Is this possible?

An example data file is attached.

 

[Somendra Misra]

Hi,

Correct me if I am wrong, in your description, you are showing row 5 in formula, where as in your sample file row 5 is header, so does it mean it is row 6.

Second, the formula are relative, so if you will copy the formula the formula will pick the relative cells. Can you explain in more details what your problem is?

Regards,

 

[desert rat]

Thanks for your feedback Somendra and yes you are right I did mean row 6. Think I was just having a brain fade as I just realised the copy/paste way will work just fine.

Thanks again

 

[Jason Koo]

Anyone can advice why the order of the cell make a different in the if function calculation?

 

[Deepak]

As your nested if placing is wrong.

In a IF calculation, As soon it will through as true statement it will just ignore the remaining parts.

So here's at very first! B17 is >A10

 

[Deepak]

So,

C14 =IF(B14>$A$11,$B$11,IF(B14>$A$10,$B$10,""))

or

C14 =IFERROR(LOOKUP(B14,$A$10:$A$11,$B$10:$B$11),"")

or

C14 =IFERROR(VLOOKUP(B14,$A$10:$B$11,2),"")


Drag it down

 

[NARAYANK991]

Hi Jason ,

The reason is that execution of a formula happens in the order left to right.

Thus , if you have an IF statement such as :

=IF(test1 , action11 , IF(test2 , action21 , action22))

the first test to be performed is test1 ; if test1 returns a TRUE result , then the output is action11 ; only if test1 returns a FALSE result is test2 performed.

For instance , if the formula is :

=IF(num > 5 , "Greater than 5" , IF(num > 7 , "Greater than 7" , "Less than or equal to 7"))

The output will be as follows :

1. when num is less than 5 - Less than or equal to 7

2. when num is greater than 5 - Greater than 5

You will never see the text Greater than 7 ; thus if num = 9 , it is certainly greater than 7 , but because the tests are performed from left to right , and because the value of 9 is greater than 5 , it satisfies the first test ; the second test is never performed , and hence you will never see the text Greater than 7.

Thus , when nesting IF statements , you have to ensure that the tests are in a progression from left to right

The correct way to write the above formula would be :

=IF(num > 7 , "Greater than 7" , IF(num > 5 , "Greater than 5" , "Less than or equal to 5"))

A similar formula testing for less than conditions would have to reverse the order , as in :

=IF(num < 5 , "Less than 5" , IF(num < 7 , "Less than 7" , "Greater than or equal to 7"))

Narayan

 

[Deepak]

In a nested if when using the > then strat from min to max
& when < then min to max

 

[Jason Koo]

So,

C14 =IF(B14>$A$11,$B$11,IF(B14>$A$10,$B$10,""))

or

C14 =IFERROR(LOOKUP(B14,$A$10:$A$11,$B$10:$B$11),"")

or

C14 =IFERROR(VLOOKUP(B14,$A$10:$B$11,2),"")


Drag it down

Thank you Deepak. I got it now.

 

[Jason Koo]

Hi Jason ,

The reason is that execution of a formula happens in the order left to right.

Thus , if you have an IF statement such as :

=IF(test1 , action11 , IF(test2 , action21 , action22))

the first test to be performed is test1 ; if test1 returns a TRUE result , then the output is action11 ; only if test1 returns a FALSE result is test2 performed.

For instance , if the formula is :

=IF(num > 5 , "Greater than 5" , IF(num > 7 , "Greater than 7" , "Less than or equal to 7"))

The output will be as follows :

1. when num is less than 5 - Less than or equal to 7

2. when num is greater than 5 - Greater than 5

You will never see the text Greater than 7 ; thus if num = 9 , it is certainly greater than 7 , but because the tests are performed from left to right , and because the value of 9 is greater than 5 , it satisfies the first test ; the second test is never performed , and hence you will never see the text Greater than 7.

Thus , when nesting IF statements , you have to ensure that the tests are in a progression from left to right

The correct way to write the above formula would be :

=IF(num > 7 , "Greater than 7" , IF(num > 5 , "Greater than 5" , "Less than or equal to 5"))

A similar formula testing for less than conditions would have to reverse the order , as in :

=IF(num < 5 , "Less than 5" , IF(num < 7 , "Less than 7" , "Greater than or equal to 7"))

Narayan

 

[Jason Koo]

Thank you NARAYANK. Got it.