Calculate the amount (£) using a formula

Good morning everyone,

I am new to this forum and I require some help, if possible. There is probablly a very simple solution to my problem but is well above my level of competence. I do appologse if this is not a great challenge to you guys, I know you like a challenge!

I have started with a new company working as a self employed member of staff and therefore need to devise my own timesheet.

I have put together a spreadsheet and need to create a a number of different formulae in order to remove human error.

I have attached an extract of my excel worksheet which will hopefully help. Basically if I enter the number 4 in B3 I would like it to multiplie by the amount in B2 and totals the amount of the entire row in D3 and column in B7. I would need to do this for each cell in all columns and rows in my worksheet




Any help you can give to me would be fantastic guys.

Here's hopefull

Gordon

Hi Gordon,

Please read the yellow banner at the top of the page. Or skip to extended version of it below:

Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If there are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, empty cells between data, mixed text/numbers, etc. - please show them all, or at least indicate in text). The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

To attach an Excel file you have to do the following (paperclip icon does not work!): Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.


Basically, you will need IF formula (to check if B2 is filled , if not - no result is expected, and some text manipulating formulas as B1 on screenshot is text with number "embedded inside" and cannot be directly used for math operations in excel.

I have now posted an extract from my worksheet. Sorry!

I have amended the original slightly and is not the same as the image I attached to my original post (unable to delete from original image from post).

Thank you

You could use something like (see attachment as Pound sign does not copy well here):

Formula:

=SUMPRODUCT(Table2119123[@[L10]:[L30]],--SUBSTITUTE(Table2119123[[#Headers],[L10]:[L30]],"L",""))

=SUM(B3:B6)*SUBSTITUTE(Table2119123[[#Headers],[L10]],"L","")

Of course you could work on standard ranges like B$2:C$2 instead of Table2119123[[#Headers],[L10]:[L30]]

But general concept would be to use just numbers (10 and 30) in header and only format it as currency or accounting, to see pound sign there, for instance in such a way, as I did with results of the formulas.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Click EDIT POST to edit post #7 and you will find the link bottom right.

LOL!!! It was there, you just didn't see it. No need to click on my post to do anything!!! I'm glad you found it.

The formula in the new attachment bears no resemblance to the one that you were given in post #4!!!

Explain in WORDS what you are expecting it to do and provide an updated workbook with manually entered expected results (3-4 will do).

How about

Formula:

=SUMPRODUCT($B$2:$K$2,B3:K3)

in L3 and copy down?
B39 (and next cells right) look well for me.

See post #12.

Bored!!! You have no idea ...

I am a teacher, but also responsible for timetabling, so at the start of the Easter break, the Dep. Head Academic said, "Hey! We're doing remote learning now and Y11 have no exams, so let's write a whole new timetable that works better for remote learning and a whole new curriculum for Y11. Oh, and it needs to be ready immediately after Easter."

I haven't had time to get bored yet ...

Please start a new thread for the new issue.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.