Counting Numbers

The conditional format formula for GREEN

Formula:

Please Login or Register  to view this content.

The conditional format formula for ORANGE

Formula:

Please Login or Register  to view this content.

see attached workbook for working example

to attach a file
- click REPLY ... GO ADVANCED ... look below for MANAGE ATTACHMENTS etc

Are the cellphone numbers known to you or could they be any number?

They are different on different sheets. So say for instance I have 5 bills to do, for every bill there are different numbers on there.

And they sometimes so 27 in front other times just 0.

I would like to get that all the same

So you are analysing calls made from various cellphones and you want to be able to see
- which numbers were called more than 10 times and
- see the analysis of each one
Is that correct?

To use conditional formatting in the way you want, Excel requires an instruction to colour code cells.
To do that:
either
- Excel creates a list of phone numbers and allocates colours to each one
or
- the user provides a list of numbers and a colour for each one.

Do you have a list of valid numbers?
Or does Excel have to create it dynamically?
(the 2nd option probably requires VBA)

How many numbers called >10 times do you expect per bill?
(if there are too many, then column D will become a meaningless jumble of colours)

I may have a different solution - depends on your answers

It looks as though you want to avoid using VBA - which is fine.

If you want a different colour used for each number then you have to provide Excel with a specific instruction.
- Excel could use a table with phone numbers in one column and a colour code in the next column
- conditional formatting can then look up each number and use the colour it's been told to use in the table
(which is a bit of a burden on the user as it requires ongoing maintenance when numbers change)

To avoid that burden I will spend a bit of time exploring ways of getting Excel to automatically choose colours for you.

More later...............

Thank you Kev_

It's not that I want to avoid it, I just don't know it.

I would love to learn more of functions and formulas so that I can do this on my own (and even VBA) (My husband is a developer, so understand the very, very basics of programming). But I don't know where to start.

In the meantime I have to check the physical bills and highlight the numbers myself.

That's why I want to use excel to make it easier and more efficient.

Thank you for the help Kev_

I have looked at various options using Excel's standard conditional formatting and whilst there are lots of things you can do, it can get pretty complicated quickly. Trying to do exactly what you wanted involved a rather long formula or setting up a table, which seems a bit of an overkill given what you are trying to achieve.
So here is a workable compromise in the attached worksheet

All formatting is based on columnD
The formula is always the same (with the final value changed)
=COUNTIF(D:D,D1048576)>10


1. Values in your database replicated a few times to give me enough numbers to play with
2. FIVE identical conditional formatting rules set up each with a different value and colour
- you can have as many as you like
3. By setting them up in the correct order (starting with the smallest value) there is no need for "Less than"
- the topmost rule in the list takes precedence)

Obviously you will need to set up your own values to suit the real data
- more than one number may rest within the same range in which case they share a colour
- more ranges = fewer numbers in the same range

Let me know what you think - there are many ways to cook this cake!!

CondF03.jpg

A trick for you - see below
Avoid that problem with {F2} and going into edit mode

The images are all bottom left of display

ReadyMode.jpg

EnterMode.jpg

EditMode.jpg