Conditional Formatting
- Thread starter dk5209
- Start date
Mortadella
Member
Don't know if this'll help but it might:
33655.92Dr 33655.92 6 Dr 1 33,655.92
333655.92Dr =LEFT(C5,LEN(C5)-2)=LEN(D5)=RIGHT(C5,LEN(C5)-(E5))=IF(F5="Cr",-1,1)=+D5*G5
33655.92Dr 33655.92 6 Dr 1 33,655.92
333655.92Dr =LEFT(C5,LEN(C5)-2)=LEN(D5)=RIGHT(C5,LEN(C5)-(E5))=IF(F5="Cr",-1,1)=+D5*G5
Mortadella
Member
Sorry - I had it all spaced out the way I wanted it when I set up my reply but it all got reorganized when I clicked send post. I also see a typo so I'll try this another way.
Use C5 as your input eg 33655.92Dr
In D5 key in the formula =LEFT(C5,LEN(C5)-2) this will give you the raw value of C5
Because your numbers can vary in length you need the actual length of the number in digits (this includes the decimal point) so in E5 key in =LEN(D5) and you'll get 8
If F5 key in =RIGHT(C5,LEN(C5)-(E5))this will return Dr or Cr depending on what sign it is.
You need some way to determine what multiplier to use on your raw number. In G5 key in =IF(F5="Cr",-1,1) it will return a minus 1 for a Credit and 1 for a Debit.
In H5 you just need the formula =+D5*G5 to get the true value. I like the custom format #,##0.00_);[Red](#,##0.00) so my numbers are quite clear as to their value.
Use C5 as your input eg 33655.92Dr
In D5 key in the formula =LEFT(C5,LEN(C5)-2) this will give you the raw value of C5
Because your numbers can vary in length you need the actual length of the number in digits (this includes the decimal point) so in E5 key in =LEN(D5) and you'll get 8
If F5 key in =RIGHT(C5,LEN(C5)-(E5))this will return Dr or Cr depending on what sign it is.
You need some way to determine what multiplier to use on your raw number. In G5 key in =IF(F5="Cr",-1,1) it will return a minus 1 for a Credit and 1 for a Debit.
In H5 you just need the formula =+D5*G5 to get the true value. I like the custom format #,##0.00_);[Red](#,##0.00) so my numbers are quite clear as to their value.
