Brain fried Excel Newbie

Occasional Visitor

As a complete and utter Excel Newbie I am currently attempting to concoct a table to aid me in calculating staff and child numbers/ratio within my setting.

 

Im trying to show what available spaces we have for each day whilst also taking into consideration which of those are EYFS children for ratio purposes. 

Have successfully manage to work out formulas for majority of my table but struggling with one last row that will show the actual amount of EYFS spaces available 

 

In order to select the smaller number of two cells (available spaces A24 and EYFS available spaces A26) I originally started with =IF(A24>A26,A26,A24) which was simple, straightforward and seemed to work. Buuuuut I had two cells with digits over 10 in A24 that show the “false” higher reading in A24 despite also being true. The only way I could get a correct answer was if I altered the > to < in the formula for those cells which surely isn’t right…?

 

with a little help from the Google gods  I discovered =IF(VALUE(A26)<A24,A26,A24) which worked for the dodgy cells, so prematurely celebrated, then realised it didn’t work for the others No clue what I’m even doing let alone why at this point!

 

anyway after fiddling for a while I’ve been looking into trying other methods and formulaes instead just incase, but no surprise my trial and error method isn’t working out Have reverted back to IF where it’s safe and “simple”

 

Anyways my most recent failure was: =IF (16,A2<A24,16,A2,A24) 

So I created another row of cells #22 (16) and converted that to: =IF(A22-A2<A24,A22-A2,A24) 

i.e. if 16-9 is less than X; Show answer, if not show X

 

Obviously need I say that doesn’t work either! #VALUE!

 

Suffice to say my brain is tired, I’ve given up for today but have realised I’ve enjoyed the process enough to possibly, maybe sign myself up for an EXCEL course #gluttonforpunishment

 

in the meantime any help would be much appreciated! 

Hope that waffle makes sense and thanks Muchly

 

1 Reply

@Michegunn 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?