Aug 20 2019 11:20 AM
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.
Aug 20 2019 11:32 AM
Aug 20 2019 11:52 AM
Won't work. Just giving me "there's a problem with this formula" message, but not saying what the issue is.
Aug 20 2019 11:58 AM
Please disregard. I had a typo this formula does work. Thank you!
Aug 20 2019 12:06 PM
Sorry... I was correct the first time. It does not work. It still shows a 0 if the value is a one or below.
Aug 20 2019 12:09 PM
Aug 20 2019 12:10 PM
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.
Aug 20 2019 12:21 PM
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
Aug 20 2019 01:17 PM
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... ????
Aug 20 2019 01:20 PM
Aug 20 2019 07:30 PM
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
Aug 20 2019 07:36 PM
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???
Aug 20 2019 08:50 PM - edited Aug 20 2019 08:51 PM
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
Aug 21 2019 02:45 PM
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?
Aug 21 2019 07:54 PM
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.
Aug 22 2019 07:49 AM
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.
Aug 22 2019 08:48 AM - edited Aug 22 2019 08:50 AM
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!
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
Aug 22 2019 10:35 AM
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.
Aug 22 2019 11:23 AM
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 more information, check out this link.
Regards
Aug 22 2019 11:27 AM
@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! :)