Weird formula?

[tim.uk]

I'm trying to understand a spreadsheet written by someone long gone to another job. Amongst other things, there are some cells of the format:

=IF(RIGHT(AD32,4)-RIGHT(AD24:AD36,3)<0,"",RIGHT(AD32,4)-RIGHT(AD32,3))

I'm somewhat mystified by this. All the cells in column AD are numeric, not text, so I don't understand what the RIGHT function will do. But in the second occurrence of the function, there is a range (in this case AD24:AD36) as the first parameter. This doesn't make sense to me. Am I missing something?

Thanks for any insights.

 

[shg]

The formula assumes the rightmost 3 or 4 characters of thoise cells are numeric.

 

[tim.uk]

Thanks for the quick reply!

The contents of the relevant cells are completely numeric. But (a) the RIGHT function will return text - is this automatically converted to a number when used in eg < 0 condition? And (b) I can't work out what is returned by using a range as the first parameter!

 

[jasonb75]

Welcome to the board!

First glance interpretation without knowing more about your sheet or testing theory.

I'm guessing that it's entered as an array formula (appears as {=IF(RIGHT(AD32,4)-RIGHT(AD24:AD36,3)<0,"",RIGHT(AD32,4)-RIGHT(AD32,3))} in the formula bar).

The first part seems like an error check, it checks that AD32 is either a minimum of 4 digits in length, or, if not, is greater than all of the (single) values in AD24:AD36. If the error check fails on both counts then the formula returns a blank.

Otherwise the second part RIGHT(AD32,4)-RIGHT(AD32,3) rounds down to the nearest 1000.

 

[Trouttrap2]

If you are trying to figure out how this formula works, you might try using the formula auditing feature. ALT+T+U+F

You can confirm it the cell is text or number by typing in =isnumber(cell reference). If that comes up true you have a number, if not you have text. I can't tell why your collegue would have used text formula for a number.

 

[shg]

When numeric text is used in an arithmetic operation (here, subtraction) it gets coerced to a number.

It's hard to tell the intent from the formula. In one sense, it returns the 4th digit from the right followed by three zeros. Post an example of the data.

 

[tim.uk]

Thanks for this! It's not entered as an array, but just exactly as in my first post above.

It makes sense now...

 

[jasonb75]

If it's not entered as array then RIGHT(AD24:AD36,3) becomes RIGHT(ADxx,3) when the formula is calculated, where xx refers to the row in which the formula is located, the other 11 cells are ignored.

 

[tim.uk]

Ah, thanks for pointing this out. I'm not familiar with arrays (and obviously the original author of my worksheet wasn't either!!).

Thanks to all responders!

 

[jasonb75]

Something I missed before, as you said it is definitely not an array formula, there is no way that the formula can return a true result (blank cell) making the majority of the formula redundant.

=RIGHT(AD32,4)-RIGHT(AD32,3) should give the same results, as should

=FLOOR(MOD(AD32,10000),1000)

there are probably a few other alternatives as well.