• 541K Members
• 4,768 Online
• 644K Conversations

Highlighted
Contributor

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

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
Highlighted

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

@Budman36

Hi,

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

Regards

Highlighted

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

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

Highlighted

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

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

Highlighted

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

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

Highlighted

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

@Budman36

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

Highlighted

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

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.

Highlighted

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

@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

Highlighted

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

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... ????

Highlighted

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

@Budman36 , check this setting

Highlighted

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

@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

Highlighted

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

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???

Highlighted

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

@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".

Regards

Highlighted

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

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?

Highlighted

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

@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.

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

Highlighted

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

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.

Highlighted

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

@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:

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

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

Highlighted

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

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.

Highlighted

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

@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.

Regards

Highlighted

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

@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!

Related Conversations
DAX Powerpivot - How to solve this problem in PowerPivot
kayyeah11 in Excel on
0 Replies
IF statements and conditional formatting
clare1981 in Excel on
1 Replies
Reverse numbers and characters in worksheet
David_Gerrior in Excel on
1 Replies
Sumifs
Jsbluemoon82 in Excel on
3 Replies
Excel formula similiar to texjoin
Carlo74 in Excel on
1 Replies
everything is black and white in excel
Gold4trees in Excel on
2 Replies