How do I calculate A/L Mawbs?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
Mark Toulson said:
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?
Click to expand...

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
 
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.
 
Back
Top