|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
February 10th, 2009, 03:35 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
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!! 
|
|
#2
February 10th, 2009, 06:49 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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!!
|
|
#3
February 10th, 2009, 07:14 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
#4
February 10th, 2009, 03:34 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
#5
February 10th, 2009, 03:41 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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!!
|
|
#6
February 10th, 2009, 04:27 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
#7
February 10th, 2009, 05:30 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
#8
February 10th, 2009, 07:36 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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!!
|
|
#9
February 10th, 2009, 08:06 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
#10
February 10th, 2009, 08:58 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
