Formula results will not post into cell

[Mark@821]

Attempting Excel spreadsheet that lists 358 line items. each line has a column for precentage of on hand vs required. this is the formula:=IF(D4>F4,"100%",IF(D4=F4,"100%",IF(D4<F4,D4/F4))). This returns the proper %.
On my 2nd spreadsheet I am trying to get the overall precentage of my groups line items. My formula is:=SUM((Master!K499:K570/72)). this shows 91.67% in the formula Arguments box, but shows #VALUE in the cell. It says a value used in the formula is of the wrong data type. Any suggestions as to what I am doing wrong?
Unable to upload file.

Thank you.

 

[NARAYANK991]

Hi ,

The basic rule is never use text values such as "100%" for numeric quantities , unless you have a specific , valid reason to do so.

Why are you not entering your formula as :

=IF(D4>F4,100%,IF(D4=F4,100%,IF(D4<F4,D4/F4)))

Secondly , your formula should be made more robust by checking for the condition that F4 may be zero.

=IF(F4=0,0,if(D4<F4,D4/F4,1))

Format the cell which has this formula to Percentage.

Lastly , an IF statement can use more than one comparison sign when checking ; thus , your original formula can be condensed to :

=IF(D4>=F4,"100%",D4/F4)

Narayan

 

[Mark@821]

Thank you for the response. However, My problem lies with the 2nd part. My 2nd formula:=SUM((Master!K499:K570/72)). this shows 91.67% in the formula Arguments box, but shows #VALUE in the cell. It says a value used in the formula is of the wrong data type. This is where I am trying to rectify the problem.

 

[Khalid NGO]

Thank you for the response. However, My problem lies with the 2nd part. My 2nd formula:=SUM((Master!K499:K570/72)). this shows 91.67% in the formula Arguments box, but shows #VALUE in the cell. It says a value used in the formula is of the wrong data type. This is where I am trying to rectify the problem.

Hi Mark,
I think this is not the correct way to sum/divide; you should try:
=SUM(Master!K499:K570)/72

But the actual problem may not rectify unless you upload your file.
Regards,

 

[Hui]

Mark

A few ideas

1. Do you have a worksheet named Master?
2. Make sure it isn't spelt with a leading or trailing space
3. Because the formula =SUM((Master!K499:K570/72)) is referencing a range of cells, Does it need to be array entered?, That is press Ctrl+Shift+Enter not just Enter when you type the formula in.

4. If you don't want to use an array formula like in 3 above, this will do the same =SUMPRODUCT(--(Master!K499:K570/72)) Enter

5. I suspect Kahlid's solution is correct

 

[Mark@821]

Hui,

Thank you. #4 did the trick and everything is working now. I am basically a Novice at this and I am self taught so every once in a while I get myself in trouble. Thank you again.

 

[Hui]

I have seen people new in Excel put the Sum() function around everything
eg: =Sum(100+50)

Which is why Kahlid's solution may have been appropriate

Your formula =SUM((Master!K499:K570/72))
is dividing each cell in the range K499:K570 by 72
then adding up each answer

Where Kahlid's answer adds up the range K499:K570 and then divides by 72

In this case both return the same value, but there will be cases especially where you are using division that the same logic doesn't hold up

 

[Mark@821]

I did try Kahlid's formula and the results came out the same. it showed correctly in the Formula Argument Box but not in the cell(which still produced "#Value". Once I changed to =SUMPRODUCT(--(Master.... the cell changed to the appropiate %.

 

[SKKS]

I just found this post. I was looking to figure out why I was getting a result in in the formula Arguments box, but #VALUE in the cell. By reading Hui's post above, solution #3, press Ctrl+Shift+Enter when entering in the formula, this cleared up my problem. My formula was referencing a range of cells. The formula was =INDEX($C$2:$C$11,MATCH($E$3&"|"&$F$3,$A$2:A11&"|"&$B$2:$B$11,0))
Thank you everyone, thank you chandoo.org!