i have this formula & i want to end up with 0 value if other cells read zero
IF G27 = ZERO i get a DIV/0 ERROR
=IF(O27<=P27,O27*K27/J27)+(IF(O27>P27,O27*K27/J27+AQ27*K27/J27)+(IF(U27<=V27,U27*K27/J27)+(IF(U27>V27,U27*K27/J27+AU27*K27/J27)+(IF(AA27<=AB27,AA27*K27/J27)+(IF(AA27>AB27,AA27*K27/J27+AY27*K27/J27))))))
|
|
0
|
|
|
|
Reply
|
 Utf
|
12/28/2009 8:39:01 PM |
|
The only value which would give a DIV/0 error is if J27 was 0. [I don't
know why you refer to G27, as it doesn't appear in the formula.]
Note also that if you test for O27<=P27, you then don't need to test for
O27>P27, and similarly in the latter tests.
Also, you have numerous unnecessary parentheses which can confuse you in
reading the formula and simplifying it.
You could simplify
=IF(O27<=P27,O27*K27/J27)+(IF(O27>P27,O27*K27/J27+AQ27*K27/J27)+(IF(U27<=V27,U27*K27/J27)+(IF(U27>V27,U27*K27/J27+AU27*K27/J27)+(IF(AA27<=AB27,AA27*K27/J27)+(IF(AA27>AB27,AA27*K27/J27+AY27*K27/J27))))))
to
=IF(O27<=P27,O27*K27/J27)+IF(O27>P27,O27*K27/J27+AQ27*K27/J27)+IF(U27<=V27,U27*K27/J27)+IF(U27>V27,U27*K27/J27+AU27*K27/J27)+IF(AA27<=AB27,AA27*K27/J27)+IF(AA27>AB27,AA27*K27/J27+AY27*K27/J27)
and then to
=IF(O27<=P27,O27*K27/J27,O27*K27/J27+AQ27*K27/J27)+IF(U27<=V27,U27*K27/J27,U27*K27/J27+AU27*K27/J27)+IF(AA27<=AB27,AA27*K27/J27,AA27*K27/J27+AY27*K27/J27)
or to
=O27*K27/J27+(O27>P27)*AQ27*K27/J27+U27*K27/J27+(U27>V27)*AU27*K27/J27+AA27*K27/J27+(AB27>AA27)*AY27*K27/J27
You can trap the J27 divide by zero problem by changing that to
=IF(J27=0,0,O27*K27/J27+(O27>P27)*AQ27*K27/J27+U27*K27/J27+(U27>V27)*AU27*K27/J27+AA27*K27/J27+(AB27>AA27)*AY27*K27/J27)
As you have a common factor of K27/J27, why not take that out and change to
=IF(J27=0,0,K27/J27)*(O27+(O27>P27)*AQ27+U27+(U27>V27)*AU27+AA27+(AB27>AA27)*AY27)
?
--
David Biddulph
Mike wrote:
> i have this formula & i want to end up with 0 value if other cells
> read zero IF G27 = ZERO i get a DIV/0 ERROR
>
> =IF(O27<=P27,O27*K27/J27)+(IF(O27>P27,O27*K27/J27+AQ27*K27/J27)+(IF(U27<=V27,U27*K27/J27)+(IF(U27>V27,U27*K27/J27+AU27*K27/J27)+(IF(AA27<=AB27,AA27*K27/J27)+(IF(AA27>AB27,AA27*K27/J27+AY27*K27/J27))))))
|
|
|
0
|
|
|
|
Reply
|
David
|
12/28/2009 9:02:49 PM
|
|
Assuming that you mean "IF J27=ZERO" rather than G27, start off your IFs with
if(J27=0,0, IF(... to prevent the DIV/0 error.
Also, once you have tested for <=, there is no need to test for > as it is
the only other option.
Also, also none of your if statements contains a FALSE clause.
"Mike" wrote:
> i have this formula & i want to end up with 0 value if other cells read zero
> IF G27 = ZERO i get a DIV/0 ERROR
>
> =IF(O27<=P27,O27*K27/J27)+(IF(O27>P27,O27*K27/J27+AQ27*K27/J27)+(IF(U27<=V27,U27*K27/J27)+(IF(U27>V27,U27*K27/J27+AU27*K27/J27)+(IF(AA27<=AB27,AA27*K27/J27)+(IF(AA27>AB27,AA27*K27/J27+AY27*K27/J27))))))
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/29/2009 2:30:01 PM
|
|
|
2 Replies
173 Views
(page loaded in 0.175 seconds)
Similiar Articles: Formatting 0 values to show blank cells - microsoft.public.excel ...I am using the =IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) to return a zero value. For printing purposes I need the 0 to not show in the cell (blank cel... Need help (Average excluding zero values) - microsoft.public.excel ...This is what i have & it works but one problem there will be zero values & i need to exclude them if possible =AVERAGE(IF(AA5="L",E$5:E$65)) I hav... Surpressing rows and columns with zero values in a pivot table ...Let's say I want a report showing the number of times that my employees, grouped in offices, markets and regions, makes a particular kind of error. ... Hiding zero values - microsoft.public.excel.chartingI have a chart with data in Columns C and E. I want the columns to zero out when subtracted (ex:=E2-C2). My issue is I only want zeros in the answer c... How can I autohide rows that have "zero" values ...In the detail worksheet there are times that the estimate value for a particular component is zero. As the detail worksheet has over 1500 work items (rows) and I need ... Hide Rows if all values are zero - microsoft.public.excel ...I am trying to write a macro that will hide a row if certain cells in that row are all zero. I want the macro to apply to all rows in the worksheet... Hide data labels with "0" values - microsoft.public ...Some of my source data contains cells with "0" values that I don't want to show on my chart. ... Show zero value but hide NA - microsoft.public.excel.charting ... Hide data ... formula to a minimum value of zero if a negative number ...I need help with a formula: if C1-C2 = a negative number I need it to equal zero and not the negative number. How do I do this? It is probably r... Replace null values with zero - microsoft.public.access ...Make yourself an update query. Set the 'update to' for each field you want to change as: Nz([Field1],0) Run it. One query, one solution. ~J PS. Pie charts - 0 values - microsoft.public.excel.charting ...However, there are 0 values in the chart and I do not want to show them. ... AJP Excel Information - Exclude zero values from pie chart Automatic ... Display or hide zero values - Excel - Office.comSelect the cells that contain the zero (0) values that you want to hide. On the Format menu, click Cells, and then click the Number tab. In the Category list, click Custom. Excel - Zero Values In Charts-dont Want To Show As Zero, But As ...Zero Values In Charts-dont Want To Show As Zero, But As Blank - I am trying to create a chart from data that is in the form... - Free Excel Help Excel - Rows With Zero Values In Pivot Table. - I have a pivot ...Rows With Zero Values In Pivot Table. - I have a pivot table tied to an OLAPcube I need it to show... - Free Excel Help skip cells with zero values in chart (cells not empty)I don't want to display zeros in my line chart. The cells are not empty - they contain a formula. I have used a simple if statement to hide a zero value - however ... EXCEL: Don't want to show the zero value in excel cell.: excel ...In excel sheet cell I have a some of values from different cells, but if value is zero cell shows the zero value which I don't want to show to the user. for exp. =c12 ... 7/20/2012 9:57:27 AM
|
Search |
Post Question |
Groups |
Stream |
About |
Register
66 followers. 