How do I calculate the ratio for a range of cells in excel?

Question

I'm trying to calculate the number of females per 100 males in each state and display the result using cell arrays. If the data for any state is unavailable or producing an error, then empty cell should be shown and error should not be shown. The final values must be rounded off to closest integer. Cell B22 is the cell NSW 713414 and B30 is the cell Australia 1891599. Cell B33 is NSW 648322 and B41 is cell Australia 3611655.

Males

NSW.        713414         
Vic.        604272  
Qld.        279644  
SA.         178878  
WA.         115391  
Tas.         89380  
NT.           4144  
ACT     
Australia. 1891599  

Females

NSW.        648322  
Vic.        598728  
Qld.        222635  
SA          177196  
WA           73175  
Tas.         82323  
NT             621  
ACT
Australia. 3611655  

Number of females per 100 males

NSW     
Vic.        
Qld     
SA      
WA      
Tas.        
NT      
ACT     
Australia       

This is what I have come up with so far:

=IF(B22:B30 = "", "", ROUND(B33:B41/(B22:B30/100),0))

P.S Sorry about not formatting it correctly and if it's hard to read

Answer 1

I slightly changed your round formula. Correct if this is not right. I went with the Number of females per males definition.

Explanation first then formula. The issue appears to be how to do a validate before doing a formula. This is done using a IsNumber function that tests if a cell has number. Putting a not in front converts this to testing a cell for not having a number. The Or allows the joining of multiple tests together. If any cell is missing a number, the output will be a blank, else it is the round formula

• =+IF(Or(NOT(ISNUMBER(B22)),NOT(ISNUMBER(B33))),"",ROUND((100*B33)/B22,0))

Put this in the output NSW cell and copy down.