Bạn đang xem bài viết Colors In An If Function được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Unfortunately, there is no way to acceptably accomplish this task without using macros, in one form or another. The closest non-macro solution is to create a name that determines colors, in this manner:
Select cell A1.
Use a name such as “mycolor” (without the quote marks).
In the Refers To box, enter the following, as a single line:
=IF(GET.CELL(38,Sheet1!A1)=10,"GO",IF(GET.CELL(38,Sheet1!A1) =3,"Stop","Neither"))
With this name defined, you can, in any cell, enter the following:
=mycolorThe result is that you will see text based upon the color of the cell in which you place this formula. The drawback to this approach, of course, is that it doesn’t allow you to reference cells other than the one in which the formula is placed.
The solution, then, is to use a user-defined function, which is (by definition) a macro. The macro can check the color with which a cell is filled and then return a value. For instance, the following example returns one of the three words, based on the color in a target cell:
Function CheckColor1(range) If range.Interior.Color = RGB(256, 0, 0) Then CheckColor1 = "Stop" ElseIf range.Interior.Color = RGB(0, 256, 0) Then CheckColor1 = "Go" Else CheckColor1 = "Neither" End If End FunctionThis macro evaluates the RGB values of the colors in a cell, and returns a string based on those values. You could use the function in a cell in this manner:
=CheckColor1(B5)If you prefer to check index colors instead of RGB colors, then the following variation will work:
Function CheckColor2(range) If range.Interior.ColorIndex = 3 Then CheckColor2 = "Stop" ElseIf range.Interior.ColorIndex = 4 Then CheckColor2 = "Go" Else CheckColor2 = "Neither" End If End FunctionWhether you are using the RGB approach or the color index approach, you’ll want to check to make sure that the values used in the macros reflect the actual values used for the colors in the cells you are testing. In other words, Excel allows you to use different shades of green and red, so you’ll want to make sure that the RGB values and color index values used in the macros match those used by the color shades in your cells.
One way you can do this is to use a very simple macro that does nothing but return a color index value:
Function GetFillColor(Rng As Range) As Long GetFillColor = Rng.Interior.ColorIndex End FunctionNow, in your worksheet, you can use the following:
=GetFillColor(B5)The result is the color index value of cell B5 is displayed. Assuming that cell B5 is formatted using one of the colors you expect (red or green), you can plug the index value back into the earlier macros to get the desired results. You could simply skip that step, however, and rely on the value returned by GetFillColor to put together an IF formula, in this manner:
=IF(GetFillColor(B5)=4,"Go", IF(GetFillColor(B5)=3,"Stop", "Neither"))You’ll want to keep in mind that these functions (whether you look at the RGB color values or the color index values) examine the explicit formatting of a cell. They don’t take into account any implicit formatting, such as that applied through conditional formatting.
For some other good ideas, formulas, and functions on working with colors, refer to this page at Chip Pearson’s website:
How To Use The Excel Roundup Function
The ROUNDUP function works like the ROUND function, except the ROUNDUP function will always round numbers up. The number of places to round to is controlled by the num_digits argument. Positive numbers round to the right of the decimal point, negative numbers round to the left, and zero rounds to the nearest 1. The table below summarizes this behavior:
Digits Behavior
Round up to nearest .1, .01, .001, etc.
Round up to nearest 10, 100, 1000, etc.
=0 Round up to nearest 1
Example #1 – round to right
To round up values to the right of the decimal point, use a positive number for digits:
=
ROUNDUP
(
A1,
1
)
// Round up to 1 decimal place
=
ROUNDUP
(
A1,
2
)
// Round up to 2 decimal places
=
ROUNDUP
(
A1,
3
)
// Round up to 3 decimal places
=
ROUNDUP
(
A1,
4
)
// Round up to 4 decimal places
Example #2 – round to left
To round up values to the left of the decimal point, use zero or a negative number for digits:
=
ROUNDUP
(
A1,
0
)
// Round up to nearest whole number
=
ROUNDUP
(
A1,
-
1
)
// Round up to nearest 10
=
ROUNDUP
(
A1,
-
2
)
// Round up to nearest 100
=
ROUNDUP
(
A1,
-
3
)
// Round up to nearest 1000
=
ROUNDUP
(
A1,
-
4
)
// Round up to nearest 10000
Example #3 – nesting
Other operations and functions can be nested inside the ROUNDUP function. For example, to round the result of A1 divided by B1, you can use a formula like this:
=
ROUNDUP
(
A1/
B1,
0
)
// round up result to nearest whole number
Rounding functions in Excel
To round normally, use the ROUND function.
To round to the nearest multiple, use the MROUND function.
To round down to the nearest specified place, use the ROUNDDOWN function.
To round down to the nearest specified multiple, use the FLOOR function.
To round up to the nearest specified place, use the ROUNDUP function.
To round up to the nearest specified multiple, use the CEILING function.
To round down and return an integer only, use the INT function.
To truncate decimal places, use the TRUNC function.
Using Strong Words — Feelings And Colors
Do you collect words? Many writers do. I know a writer who carries a notebook with her and records luscious words she encounters so she can use them in the books she writes, and in fact her novels feature such a beautiful vocabulary they are a pleasure to read. Using just the right word in just the right moment provides a thrill for writer and reader alike.
“He was mad.” “He was mad!” “He was very mad.” “He was infuriated.”
“She was sad.” “She was really sad.” “She felt empty.”
Aren’t those bolded sentences stronger than the others? Don’t you understand him, and her, much better? And just as important, aren’t those sentences much more interesting? I want to know why she feels empty! The fact that he is infuriated injects energy into whatever surrounds that sentence.
It also becomes boring to read about red roses, “bright red” lipstick, or mocha skin, when there are so many brilliant color words! Take a trip to a paint store and look at the varied (and sometimes crazy) names of colors, but choose carefully. Sangria lipstick … well, both weird and confusing. Berry lipstick, yes. (See a whole bunch of color words below!)
Find words for specific emotions, evocative colors, and strong verbs to make your sentences alive in your readers’ minds. Another benefit of seeking stronger words is that you step outside cliche; alabaster skin is so commonly used for pale white skin that most people only think of skin when they hear or read the word alabaster, right? Boring, and you do not want to be a boring writer — obviously, because you’ve read this entire post.
Copy editing can not only enrich and elevate your writing, it can also be a kind of teacher! If you study careful, artful copy editing, you’ll learn where your writing tends to go soft and how to make it sing. I care a lot about good writing—I’m enthusiastic about it, exuberant about it—and also about helping my clients learn how to think critically about their own writing. Get in touch today to talk about your work and how we might collaborate to make it sing a little louder. Email me at lori@clearvoiceeditors.com!
Save or share:
Like
Loading…
How To Use The Excel Timevalue Function
Sometimes, times in Excel appear as text values that are not recognized properly as time. The TIMEVALUE function is meant to parse a time that appears as a text value into a valid Excel time. A native Excel time is more useful than text because it is a numeric value that can be formatted as time and directly manipulated in a formula.
The TIMEVALUE function takes just one argument, called time_text. If time_text is a cell address, the value in the cell must be text. If time_text is entered directly into the formula it must be enclosed in double quotes (“”). Time_text should be supplied in a text format that Excel can recognize, for example, “6:45 PM” or “18:45”. TIMEVALUE ignores dates if present in a text string.
The TIMEVALUE function creates a time in serial number format from a date and/or time in an Excel text format. TIMEVALUE will return a decimal number between 0 and 0.99988426, representing 12:00:00 AM to 11:59:59 PM. Because the maximum value returned by TIMEVALUE is less than 1, hours will reset every 24 hours (like a clock).
Examples
The formulas below show the output from TIMEVALUE:
=
TIMEVALUE
(
"12:00"
)
// returns 0.5
=
TIMEVALUE
(
"12:00 PM"
)
// returns 0.5
=
TIMEVALUE
(
"18:00"
)
// returns 0.75
To display the output from TIMEVALUE as a formatted time, apply a time number format.
Alternative formula
Notice that the TIMEVALUE formula in C15 fails with a #VALUE! error, because cell B15 already contains a valid time. This is a limitation of the TIMEVALUE function. If you have a mix of valid and invalid dates, you can use the simple formula below as an alternative:
=
A1+
0
The math operation of adding zero will cause Excel will try to coerce the value in A1 to a number. If Excel is able parse the text into a proper time it will return a valid time as a decimal number. If the time is already a valid Excel time, adding zero will have no effect, and generate no error.
Notes
TIMEVALUE will return a #VALUE error if time_text does not contain time formatted as text.
Cập nhật thông tin chi tiết về Colors In An If Function trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!