Microsoft Excel Formulae

[PeterStoba]

Hi,

I got bored in my IT class and ended up finding some stuff on the network I shouldn't, then my teacher made me do some spreadsheet tasks. He then challenged me to find out how to do this as he doesnt know either (typical teacher).

I need to add up the totals of some orders in a spreadsheet for each month, automatically, so it works when you insert a new row too.

Here's a mock spreadsheet to show what I need to do.

Clicky

Thanks

Peter.

[smargh]

The date column isn't a properly-formatted date, nor is there a year or a timestamp. The cost is what - £13.00? 13 pence? 13 Euros? The VAT column isn't correct, it should be a calculated field in addition to a VAT percentage column (VAT won't always be 17.5%, and VAT is different for some things). There should just be one cell for the each monthly total, with each month generated automatically from the current list of dates. Every order may have several item and qty lines with separate price (item prices change over time), item IDs, product IDs, VAT rate, total price .

Solution: reject the proposal by the company - your lecturer in this case - to do this in Excel as completely unfit for purpose and redesign it in a "proper" database. If you only have Access available, do it in that.

[SiM]

There is a subtotal function. You can even do it through the menu somewhere. I can't help you further than that as I only have open office on this pc and I cba to turn my laptop on...

[PeterStoba]

The date column isn't a properly-formatted date, nor is there a year or a timestamp. The cost is what - £13.00? 13 pence? 13 Euros? The VAT column isn't correct, it should be a calculated field in addition to a VAT percentage column (VAT won't always be 17.5%, and VAT is different for some things). There should just be one cell for the each monthly total, with each month generated automatically from the current list of dates. Every order may have several item and qty lines with separate price (item prices change over time), item IDs, product IDs, VAT rate, total price .

thanks for that, will tell him, I thought they're be an easier way, It's not an actual task, just something extra cos i'm the best I wanted the answer as it confused me too

[TAKTAK]

you'd need to construct something like this:

FOR x=4 TO 100 DO

IF bx='jan' THEN f4=f4+ex
ELSEIF bx='feb' THEN f11=f11+ex

NEXT x

etc etc