Excel 2010: How do i answer these questions with SUMIF/VLOOKUP?

HI,

I have questions to answer on a excel 2010 finance spreadsheet:

- Complete "Expense Type” column with:
(a) "Income" for account codes beginning with "4"
(b) "Pay" for account codes beginning with "51"
(c) "Non Pay" for all other account codes5. Forecast the outturn on a straight line basis rounded to the nearest £

- Forecast the outturn on a straight line basis rounded to the nearest £

- Calculate the variance between Annual Budget and Forecast Outturn. Denote an adverse variance as a negative number.

- Using a formula, complete the table in worksheet labelled Table 2.


what formulas/functions would you use for each question?

here is a screen shot of the spreadsheet, there are 1000 rows

Untitled.jpg

thanks

Assuming this is homework...Show us what formulas/approaches you've tried and we'll try to steer you in the right direction.

Can you post a sample workbook than includes the formulas/approaches you've tried (even if they don't work) and the results you want to see?

Yeah...the SUMIF criteria can be tricky sometimes.

Try something like this:

Formula:

Please Login or Register  to view this content.

or this abbreviated version that does the same thing:

Formula:

Please Login or Register  to view this content.

Is that something you can work with?

My apologies...I thought you wanted to sum the relevant items.
Try this:

Formula:

Please Login or Register  to view this content.

Better?

We need to see what you're working with.
Please post a sample workbook, replacing the sensitive data with generic sample data.

"Rule 4. Don't Private Message or email Excel questions to moderators or other members. (or Word, Access, etc.)
The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members."

Surely, you can take a few minutes to replace any sensitive information with dummy data, resulting in a postabled workbook that contains no confidential information.

ok fair point,
please see attached workbook and questions for review/opinions on how to tackle

i tried using SUMIF, AND, VLOOKUP but kept getting errors

- In question 2a i fail, i use this formula: =SUMIF(F4:F1251,4*,A3:C4)
where am i going wrong?

Here's one approach to assigning the Col_H Expense Type (per the 2(a), 2(b), and 2(c) instructions)
• Enter this list in O4:P8

Please Login or Register  to view this content.

Note the leading apostrophes for the Account Codes

• This regular formula, copied down, assigns the Expense Type

Formula:

Please Login or Register  to view this content.

Is that something you can work with?

Thank you,
Just done your solution and it worked, are you sure this is 100% correct as i was told SUMIF was needed in answering the questions?

do you know how the best way to answer questions: 5, 6, 7
specifically which formulas please

Since question 2 asks you to assign a label (Income, Pay, or Non Pay) to the Col_A cells, a SUMIF function would not work since it returns a numeric value. You'll probably need a SUMIF in the Table 2 grid.

For the other questions, show us your work and we'll help you figure it out.

can someone please help
i dont need the answer, just some guidance on the formula i should use and any extra tables like in question 2

i tried this =FORECAST(C4,A4,B4)
but got #DIV/0!