How do I calculate A/L Mawbs?

[Mark Toulson]

I would like to be able to create a formula, which I presume should be quite
easy. I would like this formula to simply add 11 to the number in the
previous cell, unless the previous number ends in 6, in which case it adds
only 4.

Any help would be greatly appreciated.

Thanks

 

[Ken Wright]

With a number in say cell A1, in cell A2

=IF(RIGHT(A1)=6,A1+4,A1+11)

or even

=A1+4+(RIGHT(A1)<>6)*7

and copy down

 

[Mark Toulson]

Thanks for this. I understand the theory behind the first, but I have just
tried it and it keeps adding 11, even if the previous number ends in 6. The
second is different again. Is there another way?

Thanks

 

[Max]

Thanks for this. I understand the theory behind the first,
but I have just tried it and it keeps adding 11, even if the
previous number ends in 6.
The second is different again. Is there another way?

Try these slight mods to Ken's suggestions:

=IF(RIGHT(A1)+0=6,A1+4,A1+11)
or
=A1+4+(RIGHT(A1)+0<>6)*7

Added a "+0" to coerce the text
returned by RIGHT(A1) to a number

 

[Myrna Larson]

You need to p ut the 6 in quotes, i.e. =IF(RIGHT(A1)="6",A1+4,A1+11)

 

[Ken Wright]

My apologies, I didn't give you the edited version, there should have been a
double unary before the RIGHT(), eg:-

=IF(--RIGHT(A1)=6,A1+4,A1+11)

or even

=A1+4+(--RIGHT(A1)<>6)*7

or as Myrna suggested, put the value 6 in quotes.

You need to match text with text and numeric with numeric - Doesn't matter which
way round, but you need to be consistent. =RIGHT() will return a text value,
whereas =--RIGHT() will coerce it to numeric if it can do so.