Correction of formula.

**hecgroups** · 01-19-2013, 02:10 AM

Hello Again,

I am writing a formula which display a cell value from other sheet. If the cells are empty from current sheet i.e. F30 & F31 then it should display the value from the other sheet. but when i enter value in F30 or F31 i.e. % it is not changing the values. Below is the formula.

Please Login or Register  to view this content.

I hope the above details are enough to solve this issue. Thanks

**FDibbins** · 01-19-2013, 02:22 AM

Without seeing what this is working with, its hard to say, but to begin with, it looks like your some of your and() functions are either unnecessary, or you are closing them too soon?

try this?

=IF(AND('Calculation Result'!B48=0,'Calculation Result'!B48=""),"",IF($F$30="",'Calculation Result'!B48,IF($F$31="",'Calculation Result'!B48,SUM('Calculation Result'!B48)*$F$30*$F$31)))

**hecgroups** · 01-19-2013, 02:33 AM

Thanks for our response Mr. FDibbins. The formula provided to me doesn't work.

Here are the details of the work.

I am displaying a values from sheet Calculation Sheet to current sheet. And on current sheet i have 5 cells which multiply i.e % to the given cell values.

Example:

I have a value 5000 from other sheet in cell and i am entering a value 1.25% in F30 and left blank F31 it should given me 6250. If i enter 1.25% in F31 by keeping the value of F30 same it should give me 7813.

Before it is working but after changing the formula =5000*F30*F31 to the above formula it is not working.

**FDibbins** · 01-19-2013, 03:00 AM

I have a value 5000 from other sheet in cell and i am entering a value 1.25% in F30 and left blank F31 it should given me 6250. If i enter 1.25% in F31 by keeping the value of F30 same it should give me 7813.

5000*1.25% will give you 62.5, not 6250. even if you add them together (ie 5000 * (1+1.25%), you will still only get 5062.5. the only way you will get 6250 is is you add 25% to 5000

Im thinking that, to give the numbers you want, your formula needs to ne something like this....

=IF(AND('Calculation Result'!B48=0,'Calculation Result'!B48=""),"",'Calculation Result'!B48*IF(AND($F$30="",$F$31=""),1,(1+$F$30)*(+1$F$31))) where both F30 and F31 = 25%

If this doesnt work for you, perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).

**hecgroups** · 01-19-2013, 03:15 AM

Still getting wrong result.Please find the sample workbook. If you help me to setup one rest i will do.

**FDibbins** · 01-19-2013, 03:26 AM

thanks for the file, it helped a lot.

Try this....
=IF(AND('Calculation Result'!B4=0,'Calculation Result'!B4=""),"",'Calculation Result'!B4*IF($F$12="",1,$F$12)*IF($F$13="",1,$F$13))

**benishiryo** · 01-19-2013, 03:49 AM

hi hecgroups, maybe just:
=IF('Calculation Result'!B4=0,"",'Calculation Result'!B4*IF($F$12="",1,$F$12)*IF($F$13="",1,$F$13))

or if what you want to do is to multiply when F12:F18 has numbers, try:
=IF('Calculation Result'!B4=0,"",PRODUCT('Calculation Result'!B4,F12:G18))

**hecgroups** · 01-19-2013, 03:50 AM

it works. Thanks for your valuable time and help. topic marked as closed and reputation added. In case if i have any problem i will get back to you.

**hecgroups** · 01-19-2013, 04:06 AM

Hai Mr. FDibbins still it is displaying 0 (Zero) if the value on other sheet is Zero. I need is blank not Zero.

**FDibbins** · 01-19-2013, 12:41 PM

Change the AND to OR

edit: a re-think of the formula....
IF('Calculation Result'!B4+0=0,0,'Calculation Result'!B4*IF($F$12="",1,$F$12)*IF($F$13="",1,$F$13))