Formula creation help

New Contributor

Hi, 

 

Is anyone able to help me create a formula in Excel (Microsoft® Excel® for Microsoft 365 MSO (Version 2201 Build 16.0.14827.20180) 32-bit) that provides the total number of hours to assign to a contract?:

 

A2 (STUDENTS): If 100 or more, the formula kicks in as 15 base hours + 28 hours per 100 students, so (28/100)*number entered in A2

B2 (STAFF): 7.5 hours per number of staff added, capped at 37.5 hours

C2 (UNITS): Value returned to be multiply based on number of units; *1 (10 units), *2 (20 units), *3 (30 units), etc. 

D2 (PRAC): Value returned to multiply by 2 if Y, leave as is if N
E2: Total hours to add to contract 

 

5 Replies

@mollfrog Perhaps like in the attached file. Rows 5,7 and 9 show the formulae used in the cells on row 3 above each of them. For information only.

Screenshot 2022-04-04 at 06.37.16.png

@Riny_van_Eekelen Thank you, but I gave you the wrong references, sorry! The table looks like the attached.

The total in V6 should remain blank until anything is entered into V2-5. 

@mollfrog You did not attach "the table". 

@Riny_van_Eekelen sorry, try now

@mollfrog Thanks for that. It doesn't really matter where you put the formulae on the grid, as long as the calculations are done correctly. I took my original file and moved it all around in the places where you want to have it. And then I tweaked it a bit to create an all-in-one formula in V6.

Screenshot 2022-04-05 at 06.43.40.png

File attached.