Does the Excel Conditional Formatting Formula Determine If the Tail of a Number Is 0?

The method is as follows: 1. Suppose the data you want to process is in column A, please insert an empty column (column B) after it, and enter the formula in the empty column

Enter the formula =if(mod(a1,1)>0,round(a1,1),round(a1,0)) into cell b1

If it's not the first line, enter it in b2 instead, and change the following a1 to a2

2. Then you pull down the cell and fill that column (column b).

3. Select all cells in column b, right-click, click "Format Cells" in the menu, and select "General" under the first page, that is, in the "Number" page

4--- Step 3 is also very important, and then you can hide column A and show or print column B as column A.

5--- you try it, I wish you a happy job.

I think you can use text directly.

If you want to use conditional formatting, you should use the mod function.

For example, mod(r1,1) = 0 means that the mantissa after rounding is 0mod(r1,0) >0, which means that the whole number is greater than 0

right is a string truncation function, and the type of its value should belong to the character type, you can put the 0 in the formula in quotation marks to try it.

If it's already set to keep 1 decimal place, then there's no way.

For example, select a1:d1, conditional formatting, formula: =and(a1<>"", a1=0), set the shading color to red in the format, OK.

The null value is greater than the zero value, so you only need to format it: red background when cell=0 is enough.

To understand that 0 is the minimum, null value 0

Select the A1:D6 region and select: Start - Conditional Formatting - New Rule;

Select: Use formulas to determine which cells you want to format;

Enter the conditional formula: =(a1=0)*(a1<>""), and set the fill color, such as red;

Once determined, the 0 value will be filled with red, while the null value will not be colored.

The null value output by the formula does not mean 0, the formula outputs null value, because the cell has a formula, so the value of the cell is not equal to 0.

So you want to judge whether the cell is null or 0, not just 0.

The problem is that kf18 does not display not equal to 0, but a blank space, which is a text space, so it can't be compared with 0.

The formula can be changed to:

if(or(kf18="",fk18>0) ,2*$e18,"")

=substitute(a1*b1,right(a1*b1,2),"00")

Applies to changing the last two digits to 0

For example, column A.

The results are displayed in b1.

Then B1 inputs.

offset($a$1,max((a1:a30<>0)*row(a1:a30))-1,)

Ctrl+Shift+Enter the array formula to adjust the cell range A1:A30 by yourself

Let's say this data is in column A.

Unexpected cell entry in column A.

index(a:a,large(if(a1:a10000<>0,row(a1:a10000),0),row(a1)))

ctrl+shift+enter

Three-key ends.

For example, if your original data is in column A, you need to remove the last character from the data in column A and replace it with the data in column B.

You can enter the formula in C1.

left(a1,len(a1)-1)&b1 and then drop down to fill in column C formula.

Straight pull with =0 is enough, and the null value is equal to 0.

If you have to write both conditions, you can use or

The if function of excel is used like this:

The if function in excel is a judgment function, it can also be said to be a branch function, which can return different values according to the different results of the conditional judgment, its basic syntax is: if(a, b, c), which is interpreted as if a is true then the function returns the result b, otherwise the result c is returned. Examples:

1. In the table below, I would like to show such results in the evaluation, with a score greater than 60 showing "passing" and less than 60 showing "failing".

2. Position the cursor in cell C2, and then enter "=if(b2>60,,) in the input area"Pass","Not qualified"), and then press Enter.

3. Position the cursor on the cell again, then place the cursor on the black square block at the bottom right of the cell (as shown below), press the cursor to drag after the cursor becomes a cross, drag until the C8 position, release the mouse, and display all the results.

4. The if function also supports nested operations, that is, the if function is used again in the function. As in the above example, we want a score less than 60 to show "fail", a score greater than 60 and less than 80 to show "good", and a score greater than 80 to show "excellent", we can use the following function:

if(b2<60,"Fail",if(b2<80,"Good","Excellent"))

In the execution process of the above function, first determine whether the value of cell b2 is less than 60, if so, the output is "fail", otherwise execute if(b2<80,"Good","Excellent"), the result of executing it will give a b2 value greater than 60 and less than 80, the output is "good", and greater than 80 is a loss of "excellent". You can enter the above official letter formula into the input box of cell C3 and see if the result is like this.

Straight pull with =0 is enough, and the null value is equal to 0.

If you have to write both conditions, you can use or(a1="",a1=0)

OK or(a1=).

Or a1*5=0 can be multiplied by any number except 0.

Add a column of "Tags":

The second line writes the formula:

if(sum(a1:n1)=0,"0","n"n1 indicates the last column, which is filled in as it actually is.

0","n"As an indicator of whether it is 0 or not, you can customize it at will.

Then pull down the fill.

Select All Worksheet --> Format --> Conditional Formatting.

Formula =and(sum(1:1)=0, count(1:1)>0) format button --> pattern --> red.

--Where the red row is displayed, the data is all 0

Take the problem as the content (the subject of the email must contain "excel", I use this as a basis to identify non-spam emails, so as not to delete by mistake), excel sample file (please pay special attention: to the 03 version (if it is after 03, "save the file" as, the type box can be selected 03), and express the current situation and target effect) as an attachment to see it.

Are you a column of data? Such as column A.

Select Column A, Conditional Formatting, Formulas.

right(a1)="0"

Format selection shading, red, OK.

Do you mean that it must be the last digit of an integer?

Are you a column of data? For example, column A, determine that the ten digits of any number are 0, and select column A, conditional formatting, and formula.

left(right(int(a1),2))="0"

Format selection shading, red, OK.

For example, the single digit of any number is judged to be 0

Select Column A, Conditional Formatting, Formulas.

right(int(a1),1))="0"

Format selection shading, red, OK.

In conditional formatting, use the formula =or(right(a1)="0"), and then set to red.

Select the target cell range, and then set the conditional formatting formula as follows: (take b1 as the uppermost left cell in the selected region as an example) formatting

Conditional formatting. Formula: =right(b1,1)*1=0

Then set the format OK.