Having trouble combining 4 cells with formulas into one cell formu

J

jimtmcdaniels

I've got formula in 4 cells (fourth cell is the end result I need), that I've
tried to combine into one long formula in one end result cell, but I can't
get it to work. I'm still learning formula aughh.
If someone can combine them for me that would be awsome because I'm starting
to go cross eyed!

Cell A1 - is customer take a ticket number being served i.e. =J50

Cell B1 - is customer take a ticket number being issued i.e. =A10

Cell C1 =100*(CODE(UPPER(LEFT(A1,1)))-65)+VALUE(RIGHT(A1,2))
"FORMULA TO CONVERT CELL A1 TO A NUMBER VALUE" i.e. =950

Cell D1 =100*(CODE(UPPER(LEFT(B1,1)))-65)+VALUE(RIGHT(B1,2))
"FORMULA TO CONVERT CELL B1 TO A NUMBER VALUE" i.e. =10

Cell F1 =MOD(D1-C1, G1)
"FORMULA TO SHOW THE NUMBER OF CUSTOMER NUMBERS WAITING TO BE SERVED - BASED
ON NUMBER OF TICKETS OUT AND HOW HIGH IN THE ALPHABET THE ROLL OF NUMBERS GO
BEFORE STARTING AGAIN AT #A00." i.e. =60

Cell G1 =ABS(((CODE(UPPER(LEFT(H1,1)))-65) - (CODE(UPPER(LEFT(I1,1)))-65)
))* 100 + 100
"FORMULA TO COUNT HOW MANY TICKETS ARE ISSUED BEFORE STARTING TICKET #A00 IS
ISSUED AGAIN." i.e. =1000

Cell H - is the starting letter of the alphabet the number rolls start with.
i.e. =A

Cell I - is the ending letter of the alphabet the number rolls end with.
i.e. =J

I'm trying to eliminate Cells C1, D1, G1 by combining their formula into the
formula in Cell F1.
 
B

Bob Phillips

=MOD((100*(CODE(UPPER(LEFT(B1,1)))-65)+VALUE(RIGHT(B1,2)))-
(100*(CODE(UPPER(LEFT(A1,1)))-65)+VALUE(RIGHT(A1,2))),
ABS(((CODE(UPPER(LEFT(H1,1)))-65) - (CODE(UPPER(LEFT(I1,1)))-65)))*100+100)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top