Sliding scale prices #VALUE! error

[Worldparty]

Hi, I was helped by several experts on this forum a couple of weeks ago to produce a sliding scale pricing spreadsheet.
Unfortunately I was so focused on asking about the sliding scale aspect of the spreadsheet, I omitted to mention that it needed a column for the quantities of each panel, which is essential... stupid I know! I've had a go at adding the column myself, but whilst it works, when any cell in the column that gives the final price (column G) is empty, it's returning the error... #VALUE!
Also I need the row that's currently totaling column E (and which moves down as dimension rows are added), to now total column G instead. And ideally the word TOTAL to appear before it in column F.
I've attached the spreadsheet as it is now, and if anyone can help with this I'd be very grateful. Thank you.
Regards,
Julian.

[sandy666]

maybe use IFERROR(your_formula,"") or IFERROR(your_formula,0)

[Worldparty]

Hi Sandy666, thank you very much, that worked great! As you probably realise I'm a complete novice with Excel.
I don't suppose you or any one else, would be able to help me with the other thing I mentioned that I needs changing, which is...
"Also I need the row that's currently totaling column E (and which moves down as dimension rows are added), to now total column G instead. And ideally the word TOTAL to appear before it in column F"
I hope that makes sense?
Julian.

[sandy666]

Use Excel Table. (select A2:G11 in your example) and use Ctrl+T, then right click on this table, find Table and select Totals Row, then slect from the bottom totals row this function what you wish to see

you'll need change your formulas
D3: =IF(A3*B3*C3/1000000=0,"",A3*B3*C3/1000000)
E3: =IFERROR(IF($C3=1,VLOOKUP($D3,$I$2:$J$40,2,1),VLOOKUP($D3,$H$2:$J$40,3,1)),"")

check it by yourself

edit:
and if you want to add row, click on the last bottom right cell of the table and use TAB key

[Worldparty]

Hi Sandy666,
Thanks for your explanation, I tried to do that but couldn't make it work. I'm afraid my knowledge of Excel is so little that I can't figure it out.
Thank you for trying to help though, & thanks for solving the #VALUE! issue. But I think I'm going to have to find someone who can change the TOTAL row for me.
Best regards,
Julian.

[sandy666]

see attached file (and re-read previous post because was edited)

btw. I forgot about IFERROR in column G. do it by yourself
Do you want me to prepare workbook with step-by-step?

[Worldparty]

Thank you for doing that, but unfortunately it doesn't seem to work how I need it to. I need to be able to add as many rows of panel sizes as required for a given job, this could be just 3 or 4, or it could be 100. So the cell that has the word SUBTOTAL in it, and the cell with the total figure in it needs to move down each time I enter an additional row of dimensions.
The way that the original spreadsheet worked in this respect was perfect, but having added the quantity & final cost columns (F&G), I needed the total figure to be in column G rather than column E. If there's someway of just moving the current word SUBTOTAL & the totalled figure across from columns D&E to columns F&G that would be perfect.
I'm sorry if my explanation isn't very good, and if I'm asking too much please let me know.
Best regards,
Julian.

[AliGW]

In D3:

=IF(A3*B3*C3/1000000=0,"",A3*B3*C3/1000000)

In E3:

=IF(AND(C3="",C2<>""),"SUBTOTAL:",IFERROR(IF($C3=1,VLOOKUP($D3,$I$2:$J$40,2,1),VLOOKUP($D3,$H$2:$J$40,3,1)),""))

In G3:

=IF(E3="SUBTOTAL:",SUM($G2:G3),IFERROR(E3*F3,""))

Drag copy down.

[Worldparty]

Hi again Ali, thank you for jumping in again! As you can see I'm still trying to get this spreadsheet working properly. Embarrassed to say that I omitted a crucial part of it last time.
I did what you said, & initially it seemed to work, but when I added another couple of rows of dimensions the total figure stopped appearing.
Best regards,
Julian.

[AliGW]

Use the formulae I gave you in post #8. I am not going to have to copy and paste them in for you, surely?

[AliGW]

It works perfectly here ...

[Worldparty]

No, I did copy & paste them in, and drag copied them down, but like I said initially it seemed to work, but when I added another couple of rows of dimensions the total figure stopped appearing.

[Worldparty]

There's no SUBTOTAL figure in column G though?

[JohnTopley]

Try this in G3

=IF(E3="TOTAL:",SUM($G$2:G2),IF(C3<>"",E3*F3,""))

[Worldparty]

HA! Yes that's it! Thank you AGAIN for your help Ali, much appreciated. I'll close this thread now.
Best regards,
Julian.

[Worldparty]

Oh... sorry I just noticed that John Topley sent that last post. Thank you for that John, hopefully that's the final piece of the jigsaw.
Thanks again to everyone, Sandy, Ali & John, who helped me with this, I really appreciate it.
Best regards,
Julian.

[Worldparty]

Despite "Adding reputation" for Sandy & John, I can't add to yours again Ali, I'm getting the message... "You must spread some Reputation around before giving it to AliGW again."
So I can only thank you again.
Julian.

[sandy666]

O! I c problem is solved
You are welcome and thanks for the feedback, Julian

[AliGW]

Despite "Adding reputation" for Sandy & John, I can't add to yours again Ali, I'm getting the message... "You must spread some Reputation around before giving it to AliGW again."
So I can only thank you again.
Julian.

That's kind - don't worry about the rep points: as long as the problem is solved, I'm happy.