Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
andrum94 pe Simpatie.ro
 | Femeie
24 ani
Galati
cauta Barbat
27 - 80 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2228
|
|
I'm raising money to buy something, putting some in the pot every week.
I want Excel to color the sum differently as the value I have to raise decreases.
I also want to make this not fixed, but row() and column() dependent, relative. So if I copy the formatting to another place it won't keep my $ column.
Now to get practical, I want:
- in a cell a difference between a number (total amount I have to raise) and the sum in a range (sum of weekly raised money)
- to the left of that cell there is the text Remaining
- I want to color both the text and the value depending on the value, on these intervals: = 0, > 0, > 100, > 250, > 400
- conditional formatting is made in the order above, so I start with = 0 and go up, in the end > 400 being on the top of the conditional formatting rules and = 0 on the bottom
- in that order because from top to bottom in the rules list it's:
> 400
> 250 but <= 400
> 100 but <= 250
> 0 but <= 100
= 0
This is the result I want to achieve:
These are the formulas I used in Conditional formatting on the cell with the value, starting to make them from = 0 to > 400 so = 0 is at the bottom of the rules and > 400 on top:
=INDIRECT(ADDRESS(ROW();COLUMN()))=0
=INDIRECT(ADDRESS(ROW();COLUMN()))>0
=INDIRECT(ADDRESS(ROW();COLUMN()))>100
=INDIRECT(ADDRESS(ROW();COLUMN()))>250
=INDIRECT(ADDRESS(ROW();COLUMN()))>400
For the Remaining text, I simply added +1 to the column, like this:
=INDIRECT(ADDRESS(ROW();COLUMN()+1))=0
=INDIRECT(ADDRESS(ROW();COLUMN()+1))>0
=INDIRECT(ADDRESS(ROW();COLUMN()+1))>100
=INDIRECT(ADDRESS(ROW();COLUMN()+1))>250
=INDIRECT(ADDRESS(ROW();COLUMN()+1))>400
This way, regardless where I copy the above formatting to another similar cell duo, I have no problems with formulas in conditional formatting pointing to the previous ranges.
Source: not applicable
_______________________________________
|
|
| pus acum 2 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
updated title as it was incomplete
|
|
| pus acum 1 an |
|
