Formula issues getting blank rather than zero

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
320
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
What am I doing wrong

I am using this formula in cell E7 successfully =IF(D7-C7<0, 1+D7-C7, D7-C7).

But when cell C7 has no data I do NOT want ‘0’ to appear in E7. I have tried various formulas including this one =IF(C7=””,””,D7-C7<0, 1+D7-C7, D7-C7) but it doesn’t work.

Can someone please help me rectify the formula so that cell E7 remains blank until data is entered in C7.

Thank you
 
i think fluff has it
Possibly, but I'm not so sure. Suppose C7 and D7 were both 4 and F7 & I7 were blank. Then E7 formula would give 0, H7 & K7 would be ""
In that case I suspect that the legitimate answer of adding E7, H7 and K7 is 0 whereas Fluff's formula would return "".

Therefore, my thought is that this is what may be needed.

=IF(LEN(E7&H7&K7),SUM(E7,H7,K7),"")
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi Fluff & Squidd, This works thank you, but now causes an issue with my FINAL formula

=INT(L13)*24+HOUR(L13)+ROUND(MINUTE(L13)/60,2) which works fine if there is data in cell I use your formula in, but when it is blank I get #VALUE ! Can you further help me with this formula?

Thanks again Paul
 
Upvote 0
Hi Peter, yes C is the start time D is the end time (session 1) F is the start time of Session 2 and G is the end time. The exact same with I and J with the total then being shown in L (In the Time Format) M then converts this to Decimal O is for inputting what the individual is claiming for payment P then shows the residual amount of time which is then added to Q which is a running total of hours and minutes accumulated to allow Flexi time off in the future. Therefore if no hours are recorded but 8 hours are taken off the individual simply shows how many hours they are claiming for payment which then deducts this from their 'bank hours'. Fluff's formula appears to have done the trick. Thanks again, I genuinely appreciate everyones help. Paul
 
Upvote 0
Hi Fluff, Perfect thank you. Sorry for the delay in getting back to you but had to pop out. Just tried it and everything is great now. Really appreciate your help. Paul
 
Upvote 0
Sorry, one last issue I have discovered. For example when I use the following formula at P23

=IF(M23="","",M23-O23)

everything is fine so long as M23 has data in the cell.

M23 contains the total hours worked by that person on a particular date.

However, if that person decides not to work that day, which means that M23 will remain blank as I want it to.

But if they record that they have take time off (using their banked time) in O23, I want P23 to show a minus figure. E.G. If they show 8 (hours) off in O23 I want P23 to show -8 rather than staying blank.

I again would be very grateful for your help.

Thanks Paul
 
Upvote 0
How about
=IF(AND(M23="",P23=""),"",IF(M23="",-P23,M23-O23))
 
Upvote 0
Hi Fluff, it returned a zero in P23, not a minus figure I was hoping for as I had placed 8 in O23.

Excel displayed the following message,

"There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulae to different cells".

I am basically asking that Cell P23 remains blank unless there is data in either M23 or O23. If any number is entered in O23 when M23 is blank then in P23 the figure should be the the same as O23 but as a minus.

I hope this assists.

Thank you so much. Paul
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top