advanced excel seminar training - if formula

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » advanced excel seminar training - IF Formula

advanced excel seminar training - IF Formula

resolvedResolved · 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


 

Excel tip:

Adding cells, Rows & columns

place your cursor on a cell, row number or column letter and use CTRL + SHIFT + + or CTRL + + depending on which + you prefer to use.

View all Excel hints and tips


Server loaded in 0.09 secs.