Log In
Register
Home
New Member
July 27, 2022
Offline
Hi all,
I hope you can help me. I'm quite a newbie with formulas, being someone who can Google to find out what formula to use but I'm a bit thick when it comes to understanding how they work.
I have been tasked in our office with adding stuff to our spreadsheet, I'm the best of a bad bunch when it comes to Excel!! The spreadsheet is used as a tracker and also for budgeting and reporting.
There are 2 nested formulas that I just can't get my head around.
1. We need to report on the number of places that took the course in A, but only the professions listed in the formula Column L) so, Nursing, Art Therapy, Dietetics, Music Therapy, Occupational Therapy, Physiotherapy, Podiatry, Speech & Language Therapy. It needs to include that for some of these there were Multiple staff attending.
The formula we have provides the numbers but it isn't excluding the professions we don't need to report on. Can you please look at the formula in column AP and AR and let me know a formula that will work?
2. We need to report on the cost per head of each course for the same professions above, excluding the others, to a maximum of £1000. I have done that formula but also need a formula in column AV to show the cost difference between what we are reporting and what was actually spent, as some courses cost over £1000. The formula I have at the moment gives me cost difference for everything under £1000 so if a cost is £65 it gives a difference now of -£935. I need to to see the difference of S90 figures from the professions in L90, also allowing for multiple people K90 costs but I only want to see figures when they are above £1000 limit stipulated.
I hope this makes sense.
Thanks
Jayne
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Jayne,
Can you please upload a sample file with your data structure?
Will be easier to understand your scenario and build a functional formula.
Thank you
New Member
July 27, 2022
Offline
Hi Catalin,
Thank you for replying.
I thought I had uploaded my spreadsheet, which shows the formulas I have in place that aren't working! I'll try adding it again. Turns out it exceeded the size so I've removed some un-needed data but left all of the columns in place.
Thank you so much for your help.
Best wishes
Jayne
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Jayne,
In AV29, try this formula:
=IF(OR(S29<=1000,ISNUMBER(MATCH(L29,{"Art Therapy","Dietetics","Music Therapy","Occupational Therapy","Physiotherapy","Podiatry","Speech & Language Therapy"},0))=FALSE),NA(),S29/IF(I29="Multiple",K29,1)-1000)
For AR29, this one should work:
=IF(AND(I29="Multiple",ISNUMBER(MATCH(L29,{"Art Therapy","Dietetics","Music Therapy","Occupational Therapy","Physiotherapy","Podiatry","Speech & Language Therapy"},0))),K29,1)
1 Guest(s)
