I need a specific formula in excel

A

ASMITH

Educational use:
Using excel to set pupil targets. Each target level is subdivided into three
sub levels.ie Level 3 a,b,c; Level 4 a,b,c etc. Is there an excel formula
that could automatically raise a level/sublevel by a margin of two, ie raise
a level 3b to a 4c etc?
Any help would be appreciated
 
R

Roger Govier

Hi

With Level 3c in J5 and number to be added (2) in K5
=--(LEFT(J5))+CODE(RIGHT(J5))+K5-99&CHAR(M5+K5-1)
will work for the specific example, but not as a general result.

If your tables is set out as
J1 3a
J2 3b
J3 3c
J4 4a
J5 4b
J6 4c

Then with your uplift in column K
=OFFSET(J1,K1,0)
will work

If your list is descending i.e. from 4c down to 3a then
=OFFSET(J1,K1*-1,0)
 
Top