nested if functions

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

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Nested IF Functions

Nested IF Functions

resolvedResolved · Medium Priority · Version 365

Daniel has attended:
Excel Intermediate course
Excel Advanced course

Nested IF Functions

Good Morning

I'm working on automating one of my Project reports.

Currently I calculate a Projects Managers Utilisation using a Pivot Table and then manually work out their allocations to provide an overall % of their time for each project and then sum them.

Where I'm struggling is I've written a large Nested IF function (which I feel is rather over complicated) though when It moves from one Project Band to another it won't carry over correctly.

My formula description is Project Band*Project Stage% = Utilisation

I've got the Project Band letter A in cell A4 then I ask excel if cell A4 matches the correct band to then calculate the overall weighting formula for each row below. Cell A4 is absolute as I want the A projects to calculate using that cell. I feel like it needs to be relative because I want this one formula to work if i drag it down though if it's relative it doesn't pick up the project band after cell A4.

Project Band B Projects start in Cell A25 and then the original formula doesn't work if A4 is relative but if Absolute it still doesnt work because it's still pulling from the A projects cell.

Is there a way I can associate the cells below A4 as A projects until they get to B projects in cell A25? or a way to adapt the current formula to apply to the B projects from A25?

I'll include the formulas I've used to calculate A projects.

=IF($A$4=$K$4,IF(A4=$L$11,$M$4*$M$11,IF(A4=$L$12,$M$4*$M$12,IF(A4=$L$13,$M$4*$M$13,IF(A4=$L$14,$M$4*$M$14,IF(A4=$L$15,$M$4*$M$15,IF(A4=$L$16,$M$4*$M$16,IF($A$4=$K$4,IF(A4=$L$11,$M$5*$M$11,IF(A4=$L$12,$M$5*$M$12,IF(A4=$L$13,$M$5*$M$13,IF(A4=$L$14,$M$5*$M$14,IF(A4=$L$15,$M$5*$M$15,IF(A4=$L$16,$M$5*$M$16,IF($A$4=$K$4,IF(A4=$L$11,$M$6*$M$11,IF(A4=$L$12,$M$6*$M$12,IF(A4=$L$13,$M$6*$M$13,IF(A4=$L$14,$M$6*$M$14,IF(A4=$L$15,$M$6*$M$15,IF(A4=$L$16,$M$6*$M$16,IF($A$4=$K$4,IF(A4=$L$11,$M$7*$M$11,IF(A4=$L$12,$M$7*$M$12,IF(A4=$L$13,$M$7*$M$13,IF(A4=$L$14,$M$7*$M$14,IF(A4=$L$15,$M$7*$M$15,IF(A4=$L$16,$M$7*$M$16,0))))))*B4,IF($A$4=$K$4,IF(A4=$L$11,$M$5*$M$11,IF(A4=$L$12,$M$5*$M$12,IF(A4=$L$13,$M$5*$M$13,IF(A4=$L$14,$M$5*$M$14,IF(A4=$L$15,$M$5*$M$15,IF(A4=$L$16,$M$5*$M$16,0))))))*B4,IF($A$4=$K$4,IF(A4=$L$11,$M$6*$M$11,IF(A4=$L$12,$M$6*$M$12,IF(A4=$L$13,$M$6*$M$13,IF(A4=$L$14,$M$6*$M$14,IF(A4=$L$15,$M$6*$M$15,IF(A4=$L$16,$M$6*$M$16,0))))))*B4,IF($A$4=$K$4,IF(A4=$L$11,$M$7*$M$11,IF(A4=$L$12,$M$7*$M$12,IF(A4=$L$13,$M$7*$M$13,IF(A4=$L$14,$M$7*$M$14,IF(A4=$L$15,$M$7*$M$15,IF(A4=$L$16,$M$7*$M$16,0))))))*B4,0)))))))))))))))))))))))))


Also is there a way I can raise a question to STL including a document as i find explaining this clearly quite difficult without. I'd ensure the document is GDPR compliant.

Alternatively I can be reached seperately on d.potts@opustech.co.uk

Thank you

RE: Nested IF Functions

Hello Daniel,

Thank you for your question. It would be very helpful if you could email us a sample file. We might be able to come up with an easier, cleaner way to achieve your goal.

Please ensure that the sample data closely resembles your real data.

Please send a sample data file to:

info@stl-training.co.uk

Kind regards
Marius Barnard
STL

RE: Nested IF Functions

Hi Daniel,

Thanks for sending through the file. After having a look at it, I realised that your nested IF function doesn't work properly when copied down, because in your Pivot Table, the items A,B,C and D don't repeat down the column. Your formula would work better if they did.

It may actually be better to write the formula in your source data set and then summarising with a Pivot Table.

Also, the formula needs some fixes to work correctly. Would it be possible to send us a sample of the source data, so that we can try and make the formula work better?

Kind regards
Marius Barnard
STL

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.


 

Excel tip:

Hide columns in an Excel 2010 Worksheet

If you don’t want part of the Excel worksheet to be visible or when you don’t want certain data to appear in print outs, then a simple solution is to temporarily hide a column or multiple columns.

Hiding a single column:

1)Right click on the column header of the column you want to hide (this is the grey bar along the top edge of the worksheet)
2)Choose Hide from the menu
3)This column will now be hidden from view

Hide more than one column:

1)In the column header drag select to highlight the columns you want hidden
2)Right click and choose Hide from the menu

View all Excel hints and tips


Server loaded in 0.08 secs.