Hi trying to get a formula to work in E3, E4, E5, E6,E7 & so on



the formula is I think correct for E3, E5, E7
=IF($A3="","",IF(D3=0,B3-C3,IF(D3>=0,B3+D3-C3)))

the problem I'm having is that E4, E6 show a - when the answer should be 0

I'm new to excel and don't know how I can over come this issue

If anyone can help I'd be grateful

Attached Files

test 3.xlsx (9.8 KB, 12 views)

In your sheet you indicate that E4 "should show a 0 if B4 = 0
Why is that? There is no mention of B4 in the formula except in the calculation if D4>0 ?

EDIT Should also E5 then not be =0 ?
What is the logic?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Also note that IF(D3=0,B3-C3,IF(D3>=0,B3+D3-C3)) is the same as IF(D3>=0,B3+D3-C3,...)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi sorry for the late reply, I've been on holiday.

I'm new to excel so for me it's trial & error.

I'm trying to get the answer in column e3:E8

for row 4
If there is a 0 in column B4 ( no hours carried over from the previous financial year )
& they've had 4 hours of lessons in column C4 but they didn't block book any hours so D4=0, then the answer in column E4 should be 0 but it shows as -4. when I'm wanting the answer to show as a 0

in row 5 the person has no hours carried over from the previous financial year B5, but they have 4 hours of lessons C5 and they've block booked 10 lessons D5 so the remaining block booked hours show as 6 in E6 so that's correct.

I just can't get the answer to show as a 0 in B4 or B6

I don't know how to achieve this

Kind Regards

=IF($A3="";"";IF(AND(B3=0;D3=0);0;B3+D3-C3))

Drag down as needed

Is this better ? ( replace ; with , in the formula please)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi

This works great :-) Thank you for your help, I really appreciate it. Now I can get on and hopefully finish this piece of work :-)