Daniel has attended:
Excel Intermediate course
Excel Advanced course
Power BI Modelling, Visualisation and Publishing 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.
