• 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

A guess. See conditional formatting in ranges A1:F10 and H1:M10 of the attached.
 

Attachments

  • Chandoo42378find difference.xlsx
    9.4 KB · Views: 5
requirement (1)Is there possibility of giving four colors inside of a cell,

requirement (2)I can give you the if statements to which you have to convert them to mod formula
 

Attachments

  • experiment and result1.xlsm
    85.3 KB · Views: 6
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?
 
p45cal said:
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?
Click to expand...


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
 
Sorry I didn't refresh before posting the last time so didn't know the question as posed had been answered and the problem moved on.
Anyway, since I have it, this completes my solution to first part of the problem.
 

Attachments

  • find update - 1 - (PB).xlsx
    20.9 KB · Views: 5
@maniniinfotech
Push once < Caps Lock >

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • PLEASE DON'T SHOUT! We have big ears and will hear you just the same.
 
maniniinfotech said:
hi am unable to apply same to the other sheet can you provide me the same to the given data and format
Click to expand...
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

  • Chandoo42378find difference.xlsm
    35.4 KB · Views: 10
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
 
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

  • Chandoo42378find difference (Arr).xlsm
    30.6 KB · Views: 6
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
 
hi in the given attachment there are if statements kindly check and please provide me the mod functions
 

Attachments

  • Chandoo42378find difference (Arr) (1).xlsm
    30.9 KB · Views: 3
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

  • Chandoo42378find difference (Arr).xlsm
    24.2 KB · Views: 7
You must log in or register to reply here.
Back
Top