Using Table to convert numbers to a Positive or Negative Amount

[brandonmcg]

I have a spreadsheet with three tabs.

Tab A Final I need the Loan number and the converted amount to go here.
Tab B Table See Below
Tab C Where I paste data that needs to be converted. There is a column for a loan number and a positive or negative amount
Loan Number Amount
12345 123.56
456788 -569.78

What I need to do is get the pasted data from Tab C to Tab A while applying the rules in Tab B.

Here is the table with some examples at the bottom.

The goal is using the last number of the positive or negative number and replace the last number with the Replacement character from the table.

Kind of goofy I know but that is what I need help with.

Making any sense?

 

[Peter_SSs]

Two things that might help in the future:

1.
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

2.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


I have not done this over multiple sheets, but see if this might be any use.

22 05 27.xlsm
	A	B	C	D	E	F
1	Last Digit	Pos	Neg		Data	Result
2	1	A	J		-123	12L
3	2	B	K		-12.45	124N
4	3	C	L		-889	88R
5	4	D	M		123	12C
6	5	E	N		12.45	124E
7	6	F	O		889	88I
8	7	G	P
9	8	H	Q
10	9	I	R
11	0	{	}
Replace Digit

Cell Formulas
Range	Formula
F2:F7	F2	=SUBSTITUTE(ABS(LEFT(E2,LEN(E2)-1)),".","")&VLOOKUP(RIGHT(E2,1)+0,A$2:C$11,2+(LEFT(E2,1)="-"),0)

 

[StephenCrump]

Based on your post, I'd give the same answer as Peter, but does this make sense?

Should 123 --> 1230{ instead, because the amount is actually 123.00?

Put it another way: Is it OK that 124E could indicate an amount of either $12.45 or $1245?

 

[Peter_SSs]

Is it OK that 124E could indicate an amount of either $12.45 or $1245?

Good question.

The other one I was thinking about is whether the positive/negative amount is actually text or numerical. If numerical, and the cell displays 12.40 then the underlying number as far as Excel is concerned would be 12.4 (with the final zero being provided by the cell formatting only) so the conversion I have given would result in 12D not 124{

 

[brandonmcg]

Looks like the decimal disappears so I would say without the decimal. I updated my profile. I did download the XL2BB and I could not get it to open on my computer.

 

[brandonmcg]

I really do appreciate your help with this.

 

[StephenCrump]

Looks like the decimal disappears so I would say without the decimal.

.. which means, I think, that you want 12.4 --> 12D, which is what Peter's formula does?

Kind of goofy I know but that is what I need help with.

Making any sense?

No, not much sense the way we've done it so far, assuming this has a real-world application, and real-world consequences?

How do you translate "12D", for example. Is the loan amount 1.24, 12.4 or 124?

May we ask how the converted amounts will be used?

 

[Peter_SSs]

I did download the XL2BB and I could not get it to open on my computer.

You don't "open" it as such. You need to follow the instructions/examples on the linked page carefully.

 

[brandonmcg]

Sorry, I went on vacation. We use a very antiquated loan servicing system and this guide is what was provided by the owners of the software. This will be used for the addition and subtraction of dates or payment amounts on peoples loans via mass transactions which could be one loan or tens of thousands at a time.

What I gave you is exactly what the software people provided.

I am just now building a template so we can copy and paste data and then have the formula apply the rules you helped build and we can then run the updates.

Let me look at the guide again and see if I can provide you any other information to help answer your questions.

 

[brandonmcg]

This how the guide reads. I found out we will use it mainly for due date changes and I provided what the guide says about due dates and interest below the matrix.

Positive and Negative Values
To specify a positive amount or quantity, type the actual number or a positive replacement character in the field's last entry position.
To specify a negative amount or quantity, type a negative replacement character in the field's last entry position.
The system rejects free-form transactions that include minus (-) signs.

Final Digit of Number	Positive Replacement Character	Negative Replacement Character
1	A	J
2	B	K
3	C	L
4	D	M
5	E	N
6	F	O
7	G	P
8	H	Q
9	I	R
0	{	}

(+/-) DUE DATE ADJ The number of periods to increase or decrease the due date. The system adds the number to the existing next payment due date.
To change the due date to an earlier date, type two digits and a negative replacement character in the last position. Refer to the "Positive and Negative Values" topic for additional information.

Example: The date is 12/01/18 and the loan's period is 12 (monthly). You want the new payment due date of 10/01/18, which is two months earlier (002). Type 00 followed by the replacement character for 2.

(+/-) INTEREST ADJ The interest adjustment. The system adds the amount to interest paid year-to-date..To specify a negative value, type a negative replacement character as the last digit. Refer to the "Positive and Negative Values" topic for additional information.