Value error

without seeing the formula the only thing i can think of is that since you said you'd just copied it down from the cell above which worked fine, you might have a cell reference in the statement that should be an absolute reference but is a relative reference. if that's not the case could you post your if statement please?

You asked for it!!!

=IF('Energy Usage'!D23>SUM('Network Tariffs'!$B$24:$B$26),('Energy Usage'!D23-SUM('Network Tariffs'!$B$24:$B$26))*'Network Tariffs'!E$27+SUM('Network Tariffs'!$B$24:$B$26*'Network Tariffs'!E$24:E$26),IF('Energy Usage'!D23>SUM('Network Tariffs'!$B$24:$B$25),('Energy Usage'!D23-SUM('Network Tariffs'!$B$24:$B$25))*'Network Tariffs'!E$26+SUM('Network Tariffs'!$B$24:$B$25*'Network Tariffs'!E$24:E$25),IF('Energy Usage'!D23>'Network Tariffs'!$B$24,('Energy Usage'!D23-'Network Tariffs'!$B$24)*'Network Tariffs'!E$25+'Network Tariffs'!$B$24*'Network Tariffs'!E$24,'Energy Usage'!D23*'Network Tariffs'!E$24)))*'Customer No'!D20

Despite what it may look like, it is not a problem with absolute referencing. Any help you can give is much appreciated.

ouch! that's some formula.
actually thinking about it a bit you'd more likely get a #DIV/0! or an #NA! error or something than a #VALUE! error if it was an absolute ref problem.

i'll get my magnifiying glass out and have a look but i warn you, i am someone who is mildly addicted to excel and likes to play with it, not an expert.

The thing that really throws me is that the formula does calculate the correct answer ie if I hit f9 it shows that the formula works. It just keeps giving me a value error though and it is driving me insane!!!!

Space Bar?

Lisa,

You don't perchance have a space in one of the cells being referenced? What I'm getting at is that sometimes our users use the space bar to clear a cell as opposed to backspace. Excel will return #VALUE! if you try to perform a calculation
on the space character. You can use the find function to sped your search up a bit.

Richard

Re: Space Bar?

Richard,

The cells that I am referencing are calculated cells and do not have any spaces. I have tried hard keying numbers into these cells but this doesn't make any difference.

Any other suggestions?

Thanks.

Sorry, no other ideas (NT)

You seem to multiply the result of the IF-part with 'Customer No'!D20.
If 'Customer No'!D20 is some kind of alphanumeric ID, that might be the source of the trouble.

Aladin

#Value error usually indicates an array formula that has not been entered by C+S+E.

Your formula contains the following :-

SUM('Network Tariffs'!$B$24:$B$25*'Network Tariffs'!E$24:E$25)

What is the calculation that is required by this bit?

Yep. But that SUM-part by itself wouldn't cause a #VALUE! error.

If the whole formula is intended as an array formula and not entered as such, you might get the error in question.

Aladin

Yep. But that SUM-part by itself shouldn't cause a #VALUE! error.

If the whole formula is intended to be an array formula and not entered as such you might get the error in question.

Aladin

You said "But that SUM-part by itself wouldn't cause a #VALUE! error."

Why not?

It is an array formula. The following will produce a value error if not entered as an array formula (won't it?) :-

=SUM($B$24:$B$25*E$24:E$25)

You said "But that SUM-part by itself wouldn't cause a #VALUE! error."

Why not?

It is an array formula. The following will produce a value error if not entered as an array formula (won't it?) :-

=SUM($B$24:$B$25*E$24:E$25)

lisa
i mocked up a spreadsheet with the sheet tabs you used and some numbers and i can get the formula to return an answer, so as unlikely as it sounds i think it must be something in a cell or cells you are referencing in the formula. you might like to try what i did and see what you get:
enter all 20s in 'Energy Usage' 10s in 'Network Tariffs' and 1 in 'Customer Number'. using these numbers i get an answer of 200. doesn't provide an answer, but might tell you if the problem is somewhere in the cells referenced?
good luck - can't wait to see what the answer is.

Ah, but .....

Try doing it with all 100's in 'Energy Usage' and 10's in 'Network Tariffs'. Haven't tested it but I would think you'll get an error value.

Re: Ah, but .....

lisa

omar
i did - how do you know it would do that? i know this is not getting lisa any closer to an answer but i'm curious to know. thanks

You mean the whole formula Lisa posted? That's possible.
In that case, she could replace all SUMs in SUM-parts that sums the result of the multiplication of 2 (or more) ranges with SUMPRODUCT. You can then enter it as an ordinary formula. Also, I'm still worried about the 'Customer No'!D20 bit.

The following will produce a value error if not entered as an array formula (won't it?) :- =SUM($B$24:$B$25*E$24:E$25)

No, if there is no formula in $B$24 or $E$24, a point that I should have mentioned in my first reply to you. SUM ignore text and also treat blank cells as 0.

Aladin

Lisa - I think Omar's got it - if i array enter (Ctrl+Shift+Enter) my mockup version it seems to work

I have a nested if statement that is returning a value error. If however I go into the formula and press F9 it shows that the If statement is actually calculating the correct answer. Also, this If statement has just been copied down from the cell above where it works perfectly. Does anyone have any ideas why this is happening and how to fix it?

But excluding these implausible possibilities, a value error will occur, and in view of the fact that Lisa actually has an error value, it is surely reasonable to assume that probably not all of the cells in question are blank or contain text.

Re: Ah, but .....

: lisa

Because with your sample figures, the 'Energy Usage' figure is less than the 'Network Tariffs' total, so the array part of the formula does not get evaluated.
With my figures the "Energy" bit is greater than the "Network" bit, so the array part of the formula comes into play.

Re: Lisa - I think Omar's got it - if i array enter (Ctrl+Shift+Enter) my mockup version it seems to work

I have a nested if statement that is returning a value error. If however I go into the formula and press F9 it shows that the If statement is actually calculating the correct answer. Also, this If statement has just been copied down from the cell above where it works perfectly. Does anyone have any ideas why this is happening and how to fix it?

Re: Lisa - I think Omar's got it - if i array enter (Ctrl+Shift+Enter) my mockup version it seems to work

Thanks for all your help......It was very much appreciated. The Ctrl+Shift+Enter solved my problem and saved me hours of redoing my model!!!

Thanks

Thanks Omar, problem solved!!!!