minimum value of one to show for a formula in excel 365

Brass Contributor

I have a estimating sheet I made that has a "check box".  If the box is checked, it will take a value in a cell and minus 1 from it.  The issue is, I need it to be no less than 1, so if I have a value of 1 it shows up as a 0.  I have tried the min, minif, and nothing seems to work because it's not a range.  My current formula is =IF(U50,(L8-1),)  This works, but will show a 0 (or less) if the value in L8 becomes 1 or less. 

 

19 Replies

@Budman36

 

Hi,

 

Try this formula instead:

=IF(AND(U50,L8>1),(L8-1),1)

 

Regards

@Haytham Amairah 

Won't work.  Just giving me "there's a problem with this formula" message, but not saying what the issue is. 

@Haytham Amairah 

Please disregard.  I had a typo this formula does work.  Thank you! 

@Haytham Amairah 

Sorry... I was correct the first time.   It does not work.  It still shows a 0 if the value is a one or below. 

@Budman36

 

Could you attach the worksheet or a sample of it to figure out the problem?

@Haytham Amairah 

In playing with it, it actual gives me a value of 1 if the box is checked or not.  it does seem to work if the box is checked, but shows a value of 1 if unchecked.  Sorry for the confusion. 

@Budman36

 

You can update it as below to make it return nothing if the checkbox is unchecked!

=IF(U50=FALSE,"",IF(AND(U50,L8>1),(L8-1),1))

 

If you want it to return something else if the checkbox is unchecked, type it in between the double quotes "" in the formula.

 

Hope that helps

@Haytham Amairah 

OK.. this WORKED!!!! Good news!  However, have you seen this before?  See picture.   1 X 4 = 4.  Not 2.  This is pulling the information form the cells with your formula in them.  Does not matter if another cell equal that value, restart the computer... ????  what the heck.PNG

@Budman36 , check this setting

image.png

 

@Budman36

 

The common cause for this issue is the calculation option being set to Manual, please make sure the calculation option is set to Automatic as shown in Sergei's reply.

 

Regards

@Sergei Baklan 

It is set on Automatic.   It's very odd.  If I just take a random cell and type 1, another 4, and another "=" the two cells x each other, it coms up with 4.  But if I use the values from those cells (one cell = the cell that has the "1", and one cell = the one that has the 4, it equals 2???

 

what the heck 2.PNG

@Budman36

 

Please check the green triangle in the top-left corner of the cell as this indicates an error in the cell.

Also, check the number format of the cell, and keep them in default "General".

2019-08-21_6-48-33.png

 

Regards

@Haytham Amairah 

Ok... so I checked what you and Sergei suggested, and it didn't help.  Backing into this issue, I tried to duplicate how the green triangles came about.   The only thing I can find is that the original estimating program, that I had made, was saved as a Macro-Enabled Worksheet.  There was functions that worked with Word, that I no longer use.   If I were to take on of the original sheets, and try to modify it, it develops the triangles.  However, I am unable to save a modified version, or the original as a Macro-free workbook?  Could this be the issue?

@Budman36

 

It might be because some macros running behind the scenes!

If you no longer need these macros, you can save the workbook as macro-free.

To do so, open the workbook, press F12 to open Save As dialog box.

Then choose (.xlsx) extension from the Save as type menu and then click Save.

If you see a confirmation message like the below, click Yes.

Save workbook as macro-free.png

 

If that didn't help, please attached the workbook or a sample of it to be able to figure out the problem.

@Haytham Amairah 

 

I tried everything you said.  Even took a new sheet and did a few examples to see if it was my computer, and it works fine.  I give up!  Attached is an example.  This is a part of my estimate sheet.  Please don't tell me I have to start from scratch.  I think I will cry!...lol.  

@Budman36

 

Hi,

 

I figure it out!

In cell X51 the real value in it isn't 1 but 0.5.

Select cell X51 and press increase decimal button as follows:

Increase Decimals.png

 

What you see in the cell not always the actual value, so be careful!

To learn more about the Number Format, please watch this video.

 

This is the formula in cell X51:

=ROUNDUP(K9/2,1)

Where K9 = 1

The result is: 0.5 not 1, because 1/2 = 0.5

And when you round 0.5 up to one decimal place, the result still 0.5 because the 0.5 only has one decimal place which is 5, but if the number was 1.56, in this case, it will be rounded up to 1.60.

 

This happened because you set the second argument of the ROUNDUP function to 1.

If you want it to round the number up to zero decimal places, you need to set the second argument to 0, not 1 as follows:

=ROUNDUP(K9/2,0)

 

Hope that helps

@Haytham Amairah 

That worked! The way you explained it makes total sense also.  Thank you!  However, do I need to worry about all the other little green triangles?  They are calculating correctly, but I have no idea why they are there.  

B. 

@Budman36

 

This green triangle indicates that the cell is not locked!

By default, all cells in the worksheet are locked.

The locked and unlocked states are related to the sheet protection feature.

When you enable the sheet protection, the unlocked cells can be edited.

 

For that, Excel just warns you that this cell is not locked, and it's can be edited even if you enable the sheet protection.
 
To set back all cells to their default state, select all cells, press Ctrl+1, go to Protection tab, check the Locked checkbox, then hit OK.
Lock all cells.png

 

For more information, check out this link.

 

Regards

@Haytham Amairah Got it... thank you so much for your help!  You saved me a lot of aggravation, and an innocent computer monitor from suffering a horrible fate!  :)