|
Nesting an additional formula in an IF statement
I have an existing calculation in a column, when I drag the formula down - I
rec'v a #DIV/O - I need to insert a "Serror" in my existing formula but unsure how to complete this? My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, but I want it to show a blank cell (or 0%) so I can use the sum of M5:M66 in another cell to show an average =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) Many thanks!! |
Nesting an additional formula in an IF statement
This tweak should suffice
In M5: =IF(E5=0,0%,(H5/E5)-1) -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Karsea" wrote: I have an existing calculation in a column, when I drag the formula down - I rec'v a #DIV/O - I need to insert a "Serror" in my existing formula but unsure how to complete this? My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, but I want it to show a blank cell (or 0%) so I can use the sum of M5:M66 in another cell to show an average =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) Many thanks!! |
Nesting an additional formula in an IF statement
Karsea;222380 Wrote: I have an existing calculation in a column, when I drag the formula down - I rec'v a #DIV/O - I need to insert a "Serror" in my existing formula but unsure how to complete this? My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, but I want it to show a blank cell (or 0%) so I can use the sum of M5:M66 in another cell to show an average =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) Many thanks!! If you replace it with 0%, will not your average be wrong? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61412 |
Nesting an additional formula in an IF statement
Yes a 0% would not accurately determine the average - it would have to be a
blank cell! "Pecoflyer" wrote: Karsea;222380 Wrote: I have an existing calculation in a column, when I drag the formula down - I rec'v a #DIV/O - I need to insert a "Serror" in my existing formula but unsure how to complete this? My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, but I want it to show a blank cell (or 0%) so I can use the sum of M5:M66 in another cell to show an average =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) Many thanks!! If you replace it with 0%, will not your average be wrong? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61412 |
Nesting an additional formula in an IF statement
Hi Max,
In M5 I used your calculation but it changes my M5 to -100% (where it should actually be 0) - as H5 = 0.00 in this row "Max" wrote: This tweak should suffice In M5: =IF(E5=0,0%,(H5/E5)-1) -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Karsea" wrote: I have an existing calculation in a column, when I drag the formula down - I rec'v a #DIV/O - I need to insert a "Serror" in my existing formula but unsure how to complete this? My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, but I want it to show a blank cell (or 0%) so I can use the sum of M5:M66 in another cell to show an average =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) Many thanks!! |
Nesting an additional formula in an IF statement
Karsea;223097 Wrote: Hi Max, In M5 I used your calculation but it changes my M5 to -100% (where it should actually be 0) - as H5 = 0.00 in this row "Max" wrote: This tweak should suffice In M5: =IF(E5=0,0%,(H5/E5)-1) -- Max Singapore 'Free file hosting by Savefile.com' (http://savefile.com/projects/236895) Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Karsea" wrote: I have an existing calculation in a column, when I drag the formula down - I rec'v a #DIV/O - I need to insert a "Serror" in my existing formula but unsure how to complete this? My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, but I want it to show a blank cell (or 0%) so I can use the sum of M5:M66 in another cell to show an average =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) Many thanks!! Just to be on the safe side, could you post some of your data? Why (H5/E5)-1 -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61412 |
Nesting an additional formula in an IF statement
Okay here is a sample of my data
D E F G H I J K L M #Buy $Buy Fee TL Cost $Sold Sold Fee Date TL Cost Gain/Loss$ G/L % 5)1000 2.10 9.95 2,090.05 0 9.95 2090.05 0 0% 6)350 5.9500 9.95 2072.55 6.3500 9.95 2212.55 140.00 6.72% 7)100 19.9800 9.95 1988.05 19.6100 9.95 1951.05 (37.00) -1.85% My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, (as there is no loss it should still show as an asset - will be sold at a later date) but I want if I drag the calculation down it shows a #DIV/O in the empty cells I need it to shoew a blank cell so I can use the sum of M5:M66 in another cell to show an average % =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) *I think I have to nest another formula in (ISERR) but unsure how to do that within the existing formula Thanks again "Pecoflyer" wrote: Karsea;223097 Wrote: Hi Max, In M5 I used your calculation but it changes my M5 to -100% (where it should actually be 0) - as H5 = 0.00 in this row "Max" wrote: This tweak should suffice In M5: =IF(E5=0,0%,(H5/E5)-1) -- Max Singapore 'Free file hosting by Savefile.com' (http://savefile.com/projects/236895) Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Karsea" wrote: I have an existing calculation in a column, when I drag the formula down - I rec'v a #DIV/O - I need to insert a "Serror" in my existing formula but unsure how to complete this? My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, but I want it to show a blank cell (or 0%) so I can use the sum of M5:M66 in another cell to show an average =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) Many thanks!! Just to be on the safe side, could you post some of your data? Why (H5/E5)-1 -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61412 |
Nesting an additional formula in an IF statement
Perhaps:
=if(iserror(H5/E5), 0, H5/E5 - 1) ----- original posting ----- On Feb 9, 7:35*pm, Karsea wrote: I have an existing calculation in a column, when I drag the formula down - I rec'v a #DIV/O - I need to insert a "Serror" in my existing formula but unsure how to complete this? My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, but I want it to show a blank cell (or 0%) so I can use the sum of M5:M66 in another cell to show an average =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) Many thanks!! |
Nesting an additional formula in an IF statement
Karsea;223363 Wrote: Okay here is a sample of my data D E F G H I J K L M #Buy $Buy Fee TL Cost $Sold Sold Fee Date TL Cost Gain/Loss$ G/L % 5)1000 2.10 9.95 2,090.05 0 9.95 2090.05 0 0% 6)350 5.9500 9.95 2072.55 6.3500 9.95 2212.55 140.00 6.72% 7)100 19.9800 9.95 1988.05 19.6100 9.95 1951.05 (37.00) -1.85% My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, (as there is no loss it should still show as an asset - will be sold at a later date) but I want if I drag the calculation down it shows a #DIV/O in the empty cells I need it to shoew a blank cell so I can use the sum of M5:M66 in another cell to show an average % =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) *I think I have to nest another formula in (ISERR) but unsure how to do that within the existing formula Thanks again "Pecoflyer" wrote: Karsea;223097 Wrote: Hi Max, In M5 I used your calculation but it changes my M5 to -100% (where it should actually be 0) - as H5 = 0.00 in this row "Max" wrote: This tweak should suffice In M5: =IF(E5=0,0%,(H5/E5)-1) -- Max Singapore 'Free file hosting by Savefile.com' ('Free file hosting by Savefile.com' (http://savefile.com/projects/236895)) Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Karsea" wrote: I have an existing calculation in a column, when I drag the formula down - I rec'v a #DIV/O - I need to insert a "Serror" in my existing formula but unsure how to complete this? My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, but I want it to show a blank cell (or 0%) so I can use the sum of M5:M66 in another cell to show an average =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) Many thanks!! Just to be on the safe side, could you post some of your data? Why (H5/E5)-1 -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: 'The Code Cage Forums - View Profile: Pecoflyer' (http://www.thecodecage.com/forumz/me...pecoflyer.html) View this thread: 'Nesting an additional formula in an IF statement - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=61412) Would this help =IF((H5/E5)=0,"",(H5/E5)-1) ? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61412 |
Nesting an additional formula in an IF statement
Well, I think the problem is so... the end of the forumla says (H5/e5)-1. If
H5 = 0, then that's 0%. then you are subtracting 100%. Thus -100%. I think you want, =IF(E5=0,0%,(H5/E5)) "Pecoflyer" wrote: Karsea;223363 Wrote: Okay here is a sample of my data D E F G H I J K L M #Buy $Buy Fee TL Cost $Sold Sold Fee Date TL Cost Gain/Loss$ G/L % 5)1000 2.10 9.95 2,090.05 0 9.95 2090.05 0 0% 6)350 5.9500 9.95 2072.55 6.3500 9.95 2212.55 140.00 6.72% 7)100 19.9800 9.95 1988.05 19.6100 9.95 1951.05 (37.00) -1.85% My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, (as there is no loss it should still show as an asset - will be sold at a later date) but I want if I drag the calculation down it shows a #DIV/O in the empty cells I need it to shoew a blank cell so I can use the sum of M5:M66 in another cell to show an average % =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) *I think I have to nest another formula in (ISERR) but unsure how to do that within the existing formula Thanks again "Pecoflyer" wrote: Karsea;223097 Wrote: Hi Max, In M5 I used your calculation but it changes my M5 to -100% (where it should actually be 0) - as H5 = 0.00 in this row "Max" wrote: This tweak should suffice In M5: =IF(E5=0,0%,(H5/E5)-1) -- Max Singapore 'Free file hosting by Savefile.com' ('Free file hosting by Savefile.com' (http://savefile.com/projects/236895)) Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Karsea" wrote: I have an existing calculation in a column, when I drag the formula down - I rec'v a #DIV/O - I need to insert a "Serror" in my existing formula but unsure how to complete this? My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, but I want it to show a blank cell (or 0%) so I can use the sum of M5:M66 in another cell to show an average =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) Many thanks!! Just to be on the safe side, could you post some of your data? Why (H5/E5)-1 -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: 'The Code Cage Forums - View Profile: Pecoflyer' (http://www.thecodecage.com/forumz/me...pecoflyer.html) View this thread: 'Nesting an additional formula in an IF statement - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=61412) Would this help =IF((H5/E5)=0,"",(H5/E5)-1) ? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61412 |
Nesting an additional formula in an IF statement
Hey,
I used your calculation but it turns M5 into -100% (it should show 0% or blank) as the units were not sold at this time "joeu2004" wrote: Perhaps: =if(iserror(H5/E5), 0, H5/E5 - 1) ----- original posting ----- On Feb 9, 7:35 pm, Karsea wrote: I have an existing calculation in a column, when I drag the formula down - I rec'v a #DIV/O - I need to insert a "Serror" in my existing formula but unsure how to complete this? My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, but I want it to show a blank cell (or 0%) so I can use the sum of M5:M66 in another cell to show an average =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) Many thanks!! |
Nesting an additional formula in an IF statement
Hi,
I used your calculation but it shows 0% in M5 (which is fine) but now all my calculations in M6, M7 . . etc now show 0%(when in fact there is a calculation tobe formatted) K "Sean Timmons" wrote: Well, I think the problem is so... the end of the forumla says (H5/e5)-1. If H5 = 0, then that's 0%. then you are subtracting 100%. Thus -100%. I think you want, =IF(E5=0,0%,(H5/E5)) "Pecoflyer" wrote: Karsea;223363 Wrote: Okay here is a sample of my data D E F G H I J K L M #Buy $Buy Fee TL Cost $Sold Sold Fee Date TL Cost Gain/Loss$ G/L % 5)1000 2.10 9.95 2,090.05 0 9.95 2090.05 0 0% 6)350 5.9500 9.95 2072.55 6.3500 9.95 2212.55 140.00 6.72% 7)100 19.9800 9.95 1988.05 19.6100 9.95 1951.05 (37.00) -1.85% My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, (as there is no loss it should still show as an asset - will be sold at a later date) but I want if I drag the calculation down it shows a #DIV/O in the empty cells I need it to shoew a blank cell so I can use the sum of M5:M66 in another cell to show an average % =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) *I think I have to nest another formula in (ISERR) but unsure how to do that within the existing formula Thanks again "Pecoflyer" wrote: Karsea;223097 Wrote: Hi Max, In M5 I used your calculation but it changes my M5 to -100% (where it should actually be 0) - as H5 = 0.00 in this row "Max" wrote: This tweak should suffice In M5: =IF(E5=0,0%,(H5/E5)-1) -- Max Singapore 'Free file hosting by Savefile.com' ('Free file hosting by Savefile.com' (http://savefile.com/projects/236895)) Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Karsea" wrote: I have an existing calculation in a column, when I drag the formula down - I rec'v a #DIV/O - I need to insert a "Serror" in my existing formula but unsure how to complete this? My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, but I want it to show a blank cell (or 0%) so I can use the sum of M5:M66 in another cell to show an average =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) Many thanks!! Just to be on the safe side, could you post some of your data? Why (H5/E5)-1 -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: 'The Code Cage Forums - View Profile: Pecoflyer' (http://www.thecodecage.com/forumz/me...pecoflyer.html) View this thread: 'Nesting an additional formula in an IF statement - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=61412) Would this help =IF((H5/E5)=0,"",(H5/E5)-1) ? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61412 |
Nesting an additional formula in an IF statement
Hi,
Your calculation works great (=IF((H5/E5)=0,"",(H5/E5)-1) BUT . . . when I drag the new and improved formula down to the empty cells it still gives me a #DIV/0! THXS! "Sean Timmons" wrote: Well, I think the problem is so... the end of the forumla says (H5/e5)-1. If H5 = 0, then that's 0%. then you are subtracting 100%. Thus -100%. I think you want, =IF(E5=0,0%,(H5/E5)) "Pecoflyer" wrote: Karsea;223363 Wrote: Okay here is a sample of my data D E F G H I J K L M #Buy $Buy Fee TL Cost $Sold Sold Fee Date TL Cost Gain/Loss$ G/L % 5)1000 2.10 9.95 2,090.05 0 9.95 2090.05 0 0% 6)350 5.9500 9.95 2072.55 6.3500 9.95 2212.55 140.00 6.72% 7)100 19.9800 9.95 1988.05 19.6100 9.95 1951.05 (37.00) -1.85% My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, (as there is no loss it should still show as an asset - will be sold at a later date) but I want if I drag the calculation down it shows a #DIV/O in the empty cells I need it to shoew a blank cell so I can use the sum of M5:M66 in another cell to show an average % =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) *I think I have to nest another formula in (ISERR) but unsure how to do that within the existing formula Thanks again "Pecoflyer" wrote: Karsea;223097 Wrote: Hi Max, In M5 I used your calculation but it changes my M5 to -100% (where it should actually be 0) - as H5 = 0.00 in this row "Max" wrote: This tweak should suffice In M5: =IF(E5=0,0%,(H5/E5)-1) -- Max Singapore 'Free file hosting by Savefile.com' ('Free file hosting by Savefile.com' (http://savefile.com/projects/236895)) Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Karsea" wrote: I have an existing calculation in a column, when I drag the formula down - I rec'v a #DIV/O - I need to insert a "Serror" in my existing formula but unsure how to complete this? My formula: In M5 =IF((H5/E5)=0,0%,(H5/E5)-1) My formula works great, but I want it to show a blank cell (or 0%) so I can use the sum of M5:M66 in another cell to show an average =AVERAGEA(M5:M66) without rec'v a #DIV/0 (error) Many thanks!! Just to be on the safe side, could you post some of your data? Why (H5/E5)-1 -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: 'The Code Cage Forums - View Profile: Pecoflyer' (http://www.thecodecage.com/forumz/me...pecoflyer.html) View this thread: 'Nesting an additional formula in an IF statement - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=61412) Would this help =IF((H5/E5)=0,"",(H5/E5)-1) ? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61412 |
| All times are GMT +1. The time now is 02:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com