How to identify what the 'VALUE' error is and then sort it out

[nje]

Good Morning
In my sample workbook in sheet wheelchair 1 everything was working well until I took the dates out of column D & E and then I got the value error
I have spent 3 hours trying to sort it out and ech time I get rid of it, something in the formula won't work, it either adds up or subtracts in places I don't want it to.
It works at the moment, but I need to get rid of the 'Value' Could you help please?
Once that is done, it will be finished (I hope)
Thanks

[benishiryo]

hi nje. it has a VALUE error because you did a summation of 2 IF formulas & both returns "".
=IF(E6="","",IF(F6="","",IF($H6>0,(($G6+1)*'Master sheet'!$J$4)-SUM($J6:$O6),($G6*'Master sheet'!$J$4))))+SUM($J6:$O6)
the above returns blank
+IF(E6="","",IF(F6="","",IF($H6>0,(($G6+1)*$C6),($G6*$C6))))-SUM($J6:$O6)
so does this

so it's "" + ""

why dont you tell us your logics & let us help you come up with the formula. i dont think you need to repeat it twice. it should be something like
1) if E5 or F5 is blank, then show blank
2) if H5 greater than 0, (($G6+1)*'Master sheet'!$J$4)-SUM($J6:$O6) + (($G6+1)*$C6)
and so on

[nje]

Thank you Benhishiryo for your response.
I'll try and explain
A charity hire out scooters and wheelcahirs. With the help of this forum I put together a workbook.
The idea was to make it easy to use in relation to the charges.
Unfortunately there were initially a number of difficulties.
1 They charge by the week, or part of a week.
One week (7days) =£2
One day = £2
One week & One day (8 days) =£4
The added problem was that excel doesn't count the first day which needed to be included.
I used this formula to start with and then it started to grow.

=IF(D5="","",IF(E5="","",IF($G5>0,(($F5+1)*'Master sheet'!$I$3)-SUM($I5:$N5),($F5*'Master sheet'!$I$3)-SUM($I5:$N5))))

The control amount (hire charge) was put onto the master page to make it easy to change. But that method changes all the costs in one go and the charity wanted to retain the data on the sheet.
So I introduced Column 'C' so a change of cost could be added in.
Everything elase on the worksheet works well, it is just since I introduced column C that the trouble began and I have got totally confused because of all the changes I have mad
So what I would like is for it to work as it does at the moment, but if the date cells are empty I think that column E & F then no VALUE sign showing.
If there is an easier way to do it, then I would be grateful for any suggestions, but my knowledge is very limited
If you can understand all this, and help then it will be greatly appreciaed
Thanks

[benishiryo]

probably need a more detailed explanation from you like how i listed them. or you can try correcting my statements here:
1) if DateHired(E6) or DateReturned(F6) is blank, then show blank
2) if Days(H6) greater than 0, then (Week Hired + 1)*Hire Cost ($1) - Interim Payments
3) otherwise, Week Hired * Hire Cost ($1)
4) Interim Payments is added back
5) IF statement like (1) is repeated
6) if Days(H6) greater than 0, then (Week Hired + 1)*Extra
7) otherwise, Week Hired * Hire Cost ($1)
8) Interim Payments is deducted again

seems to me like (1) & (5) can be combined. (2) will + (6) when Days greater than 0, excluding the deduction of Interim Payments. (3) will + (7) when Days not greater than 0. (4) is removed. Deduction of Interim Payments will only be done once in (8). so maybe:
=IF(OR(E6="",F6=""),"",IF($H6>0,($G6+1)*'Master sheet'!$J$4+($G6+1)*$C6,$G6*'Master sheet'!$J$4+$G6*$C6)-SUM($J6:$O6))

set up some scenarios for testing & type the answer that it should be showing & how to get that. that's probably the best way to tell us how to help you.

[nje]

Thanks, I'll get on with that.

[nje]

I'm not any good at explaning what I want formulas to return, so I have attached a fully working earlier version.
with some explanation within the worksheet which I hope will explain the situation and what I'm trying to achieve.
The attached is what I had before I started messing about with the formula to try and include column C into the calculation.
It's obviously the wheelchair sheet where the problems are.

Thanks for your time

[nje]

Benishiryo- Many thanks for your help. Your formula from above does indeed solve my problem, but because I mistyped it I hadn't realised.
Thank you for your patience and for your expertise.