Question description:
This user has given permission to use the problem statement for this
blog.
Need help with excel formula. I want to minus 4 if the cell is greater than 0
Solved by K. Y. in 17 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
11/12/2017 - 06:52
HI, It's pleasure to help you today:)
User
11/12/2017 - 06:52
Hello.
Excelchat Expert
11/12/2017 - 06:52
Please share me sample data and formula.
Excelchat Expert
11/12/2017 - 06:53
Ok. Which cell i need to address in the preview?
User
11/12/2017 - 06:53
I need help with H19
Excelchat Expert
11/12/2017 - 06:54
Ok. What is the cell i need to refer for sum value?
User
11/12/2017 - 06:54
This is to monitor how many days someone is working a week (mon-sun). They should only be working 3 days per week. If they work over this, i want a formula to calculate the number of days they need to not work in the future to make this back to zero
Excelchat Expert
11/12/2017 - 06:56
I want to minus 4 if the cell is greater than 0? What cell i need to check for >0?
User
11/12/2017 - 06:57
It would be the number of 'G's between mon-sun
User
11/12/2017 - 06:57
might need another call to calc these
User
11/12/2017 - 06:58
Okay so
User
11/12/2017 - 06:58
A4 calculates how many Gs over 3 they have worked
User
11/12/2017 - 06:58
if this equals 1 or more, i want I20 to calculate them
Excelchat Expert
11/12/2017 - 06:58
ok.
User
11/12/2017 - 06:59
them being A4, A11 & A18
User
11/12/2017 - 06:59
In my real spreadsheet the dates go on until 2019
Excelchat Expert
11/12/2017 - 07:02
I have updated it. Please check.
Excelchat Expert
11/12/2017 - 07:02
=if(COUNTIF(D4:D10,"G")-3>0,COUNTIF(D4:D10,"G")-3,0)
Excelchat Expert
11/12/2017 - 07:02
The formula is written in A4 cell.
User
11/12/2017 - 07:02
i would epect I 20 to equal -2
User
11/12/2017 - 07:02
because she didn;t work the full 3 days
User
11/12/2017 - 07:02
she only had 1 G in the range
User
11/12/2017 - 07:02
so it should flag 2 days are not worked
Excelchat Expert
11/12/2017 - 07:03
ok
Excelchat Expert
11/12/2017 - 07:03
Then what is the condition to include >0?
Excelchat Expert
11/12/2017 - 07:03
I can change the formula to show -2
User
11/12/2017 - 07:03
equally, if she worked 4 days, i'd expect it to equal 1
Excelchat Expert
11/12/2017 - 07:03
Please check now.
User
11/12/2017 - 07:03
the reason why i want it to only count if over 0 is because
User
11/12/2017 - 07:04
at the moment, because she has not worked future dates, the cells are equalling - 3 all the way down to 2019
User
11/12/2017 - 07:04
which is should I20 as like -150
User
11/12/2017 - 07:05
but instead I am thinking if at least one day is worked, then that should trigger I20 to include it in its count
User
11/12/2017 - 07:05
opposed to counting all the -3's
User
11/12/2017 - 07:05
sorry it is hard to explain
Excelchat Expert
11/12/2017 - 07:05
I understood. I am working on it.
User
11/12/2017 - 07:06
Can you see the yellows all show -3 because she has not stated which dates she actually worked therefore I20 shows incorrect result
Excelchat Expert
11/12/2017 - 07:06
I have changed it. Please check it now.
Excelchat Expert
11/12/2017 - 07:06
=if(COUNTIF(D4:D10,"G")>0,COUNTIF(D4:D10,"G")-3,0)
Excelchat Expert
11/12/2017 - 07:06
Here is the formula. When the count is >0 then only it deduct 3, else, it stays 0.
User
11/12/2017 - 07:07
hm is that right
User
11/12/2017 - 07:07
1 sec
Excelchat Expert
11/12/2017 - 07:07
Thank you.
User
11/12/2017 - 07:08
yes it is
User
11/12/2017 - 07:08
you are very clever!
Excelchat Expert
11/12/2017 - 07:08
Have a great day ahead:). Please visit Got It Pro for new queries.
User
11/12/2017 - 07:08
thank you :)
Excelchat Expert
11/12/2017 - 07:08
Please leave a great feedback after ending the session.
User
11/12/2017 - 07:09
will do
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
