98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » advanced excel seminar training - IF Formula
advanced excel seminar training - IF Formula
Resolved · Low Priority · Version Standard
Luke has attended:
Excel Intermediate course
IF Formula
Basically this is the ugly formula I used to sum all the stages in the process by each cost, based on the month selected in the pick list. This then profiles my budget monthly for each cost.
Is there an easier way to do this??
Surely there is.
Formula: Cell B201
=IF($I$194=$S12,($H$183))+IF($I$148=$S12,($H$137))+IF($I$132=$S12,($H$121))+IF($I$116=$S12,($H$104))+IF($I$20=$S12,($H$12))+IF($I$176=$S12,($H$167))+IF($I$33=$S12,($H$25))+IF($I$46=$S12,($H$38))+IF($I$162=$S12,($H$153))+IF($I$88=$S12,($H$80))+IF($I$75=$S12,($H$67))
Dry Run
Salaried Judicial
T&S( No tax or NI)
Fee paid Judicial
Chair Fee
Panellists Fee
T&S Paid Days
T&S (incl Tax & NI)
AD Days
These are the cost repeated for 19 stages of the process.
RE: IF Formula
Hi Luke. That is some formula. Without seeing the spreadsheet itself, I'm inclined to advise you need to restructure it, splicing out each month's figures into separate worksheets and then using 3-d formulae to link them into a final "report" sheet. Alternatively, have a separate, hidden "calculate" worksheet where you can link data and perform calculations. Try to put each If function into a separate cell so that you can manipulate it individually, then perform the sum in another cell and link that into the final report sheet. Also, whatever, you do, add comments to the cells with your formulae in so it's clear when they do!
Hope this helps,
Anthony
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Adding cells, Rows & columnsplace your cursor on a cell, row number or column letter and use CTRL + SHIFT + + or CTRL + + depending on which + you prefer to use. |