Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Look UP and then caluclation with IS ERROR

7 views
Skip to first unread message

Excel Dumbo

unread,
May 10, 2013, 1:05:55 AM5/10/13
to

Hello,

Please refer attached worksheet and advise a formula to lookup and then
do a calculation and adding IS ERROR to it.

Thanks heaps

Excel Dumbo


+-------------------------------------------------------------------+
|Filename: LookUP and then do calculation with ISERROR.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=871|
+-------------------------------------------------------------------+



--
Excel Dumbo

Claus Busch

unread,
May 10, 2013, 2:36:13 AM5/10/13
to
Hi,

Am Fri, 10 May 2013 06:05:55 +0100 schrieb Excel Dumbo:

> Please refer attached worksheet and advise a formula to lookup and then
> do a calculation and adding IS ERROR to it.

if you calculate the price per unit in column F it is much easier.
In F2:
=B2/(C2-D2)*(E2="RETAIL")

In B17 you need:

=IFERROR((VLOOKUP(A17,$A$1:$E$10,2,0)/(VLOOKUP(A17,$A$1:$E$10,3,0)-VLOOKUP(A17,$A$1:$E$10,4,0)))*(VLOOKUP(A17,$A$1:$E$10,5,0)="RETAIL"),"")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Kevin@Radstock

unread,
May 10, 2013, 3:35:38 AM5/10/13
to

@ Claus Busch

For your information, IFERROR is not available in Excel 2003 or versions
prior to that one!!



Claus Busch;1611742 Wrote:
> Hi,
>
> Am Fri, 10 May 2013 06:05:55 +0100 schrieb Excel Dumbo:
> -
> > Please refer attached worksheet and advise a formula to lookup and
> then
> > do a calculation and adding IS ERROR to it.-
>
> if you calculate the price per unit in column F it is much easier.
> In F2:
> =B2/(C2-D2)*(E2="RETAIL")
>
> In B17 you need:
>
> =IFERROR((VLOOKUP(A17,$A$1:$E$10,2,0)/(VLOOKUP(A17,$A$1:$E$10,3,0)-VLOOKUP(A17,$A$1:$E$10,4,0)))*(VLOOKUP(A17,$A$1:$E$10,5,0)="RETAIL"),"")
>
>
> Regards
> Claus Busch
> --
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Kevin@Radstock

Excel Dumbo

unread,
May 13, 2013, 7:06:41 PM5/13/13
to

thanks kevin and claus


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Excel Dumbo
0 new messages