• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

finding difference between two cells

p45cal

Well-Known Member
In your COLOUR CODE 3 sheet you have many formulae of the ilk:
=IF(RIGHT(P16,1)>=LEFT(P16,1)+0,RIGHT(P16,1)+0,(RIGHT(P16,1)))
which puts either text or numeric result in the cell, depending on whether the first digit is larger thanthe last digit; Why?
 
In your COLOUR CODE 3 sheet you have many formulae of the ilk:
=IF(RIGHT(P16,1)>=LEFT(P16,1)+0,RIGHT(P16,1)+0,(RIGHT(P16,1)))
which puts either text or numeric result in the cell, depending on whether the first digit is larger thanthe last digit; Why?

is there any option based on numeric value those option work because in the given attachment it only works on text format
 
HI CAN YOU CHECK THE GIVEN ATTACHMENT
CAN YOU WRITE A SAME TYPE OF FORMULA TO THE TWO DIGITS INSIDE A CELL AS SAME AS NOW GIVEN
AND FOR THE TWO DIGITS DON CHANGE THE CELL BACKGROUND INSTEAD CHANGE THE FONT COLOR OF THE RIGHT AND LEFT
 

vletm

Excel Ninja
@maniniinfotech
Push once < Caps Lock >
  • PLEASE DON'T SHOUT! We have big ears and will hear you just the same.
 

p45cal

Well-Known Member
hi am unable to apply same to the other sheet can you provide me the same to the given data and format
In the attached is conditional formatting as I think you want it.

I have also added a macro to apply conditional formatting quickly, accurately and flexibly. There's a button on both sheets which calls the macro blah.
Before clicking the button, you must first select two areas on the sheet where you want the conditional formatting to be added; start by selecting the first area normally, then while holding the Ctrl key on the keyboard down, select the second area, then let go. Then you can click the button which:
1. Looks at the colour mapping on Sheet2 cells W1:X10 (you can change the colours in column X to suit yourself).
2. Clears all conditional formatting from your two selected areas.
3. Applies new conditional formatting to those same areas.

Conditional formatting on a sheet can get out of control - it can get resource hungry if there's a lot of it. It's very easy to keep on adding conditional formatting and ultimately it becomes a mess. It's a good idea to keep it under control by removing unecessary conditional formatting.
 

Attachments

what is the option to convert if statement to mod function
1>=(IF(LEFT(D2,1)>=LEFT(C2,1),LEFT(D2,1)-LEFT(C2,1),LEFT(D2,1)+10-LEFT(C2,1))&(IF(RIGHT(D2,1)>=RIGHT(C2,1),RIGHT(C2,1)-RIGHT(C2,1),RIGHT(D2,1)+10-RIGHT(C2,1))))

2>=(IF(LEFT(D2,1)>=RIGHT(C2,1),LEFT(D2,1)-RIGHT(C2,1),LEFT(D2,1)+10-RIGHT(C2,1))&(IF(RIGHT(D2,1)>=LEFT(C2,1),RIGHT(D2,1)-LEFT(C2,1),RIGHT(D2,1)+10-LEFT(C2,1))))

these two are to be converted to mod, is there any option kindly teach me
 

Peter Bartholomew

Well-Known Member
I do detest the normal spreadsheet convention of working with relative cell references!
It serves to emphasis the irrelevant whilst obscuring any significance the data may have.

To demonstrate the difference, I have chosen a 6x5 block of numbers from one of @maniniinfotech's workbooks and called it 'Grid'.
I then split out the two digits from each cell, creating two 6x5 arrays 'X' and 'Y'. These refer to
= QUOTIENT(+Grid, 10) and
= MOD(+Grid, 10)
respectively. The '+' signs coerce characters into numbers in case the original grid is comprised of text strings.

The derived tables are then given by
= MOD( X+Y, 10 ) and
= MOD( Y- X, 10 )
The formulas are array formulas so need to be committed with CSE in most versions of Excel (dynamic array versions excepted).

The algebraic simplification could also be made in the direct referencing versions of the formulas
=IF(LEFT(C18,1)+RIGHT(C18,1)>=10,LEFT(C18,1)+RIGHT(C18,1)-10,(LEFT(C18,1)+RIGHT(C18,1)))
=IF(RIGHT(C18,1)>=LEFT(C18,1),RIGHT(C18,1)-LEFT(C18,1),(RIGHT(C18,1)+10-LEFT(C18,1)))

but the notation itself tends to obscure any algebraic relationships within the formulas.

Why are spreadsheets like this?
1. Dan Brickin correctly observed that defining variables is 'tedious'
2. Spreadsheets are designed for users for whom 'algebra' is a nightmare left over from schooldays
3. The overwhelming majority of spreadsheets do not have any formulas so crap notation is not an issue
 

Attachments

Last edited:
There is an error when i hit the button in the #47 provided attachment
it says run time error 1004
application defined or object defined error
 

Peter Bartholomew

Well-Known Member
Sorry, I hadn't tested the VBA. As I had moved the reference colour array (I had deleted its original location) the code no longer picked it up correctly. I have now introduced a further defined Name 'CFcolours' that ensures it picks up the reference table. I also found it confusing having a sheet with the codename 'Sheet1' and tab name 'Sheet2' so I changed that to 'test'.

Please do not interpret my earlier post as a personal attack; in no way was that my intention. I like your idea of using a macro to introduce the conditional formats and I can hardly blame you for the way spreadsheets work.
 

Attachments

Top