Excel formula to match a number from a selection of data (OR and IF function)

Dark Poison · 06-23-2009, 06:29 AM

Hi there, i'm having difficulty trying to make this forumla behave itself. Basically i want a set price to be given to each station if it falls under that particular price category. The formula is as follows;

=IF(OR(I4="Hatfield", I4="Huntingdon", I4="Letchworth"),90,(IF(OR(I4="Bedford", I4="Flitwick", I4="Stevenage"),125,(IF(OR(I4="Downham Market", I4="Kings Lynn"),70,(IF(OR(I4="Luton", I4="LAP", I4="Luton Airport Parkway", I4="St Albans", I4="St Albans & Harpenden"),140),(IF(I4="Hitchin",100))))))))

now when i fill series this down my list, it works for all stations exept for Hitchin, Luton, Luton airport Parkway, St Albans & Harpenden, Letchworth and St Albans. Why is that?

I4 is the cell and column with the name of each station, of course.

ShosMeister · 06-23-2009, 07:23 AM

You had an extra ) in there after your 140 before the last IF. Aligning the (...) helped to find it. I also took out the extra ( that you have before all the IFs.

Code:

=IF(
     OR(I4="Hatfield", I4="Huntingdon", I4="Letchworth"),
     90,
     IF(
         OR(I4="Bedford", I4="Flitwick", I4="Stevenage"),
         125,
         IF(
             OR(I4="Downham Market", I4="Kings Lynn"),
             70,
             IF(
                 OR(I4="Luton", I4="LAP", I4="Luton Airport Parkway", I4="St Albans", I4="St Albans & Harpenden"),
                 140,
                 IF(
                     I4="Hitchin",
                     100,
                     "INVALID VALUE"
                   )
               )
           )
       )
   )

Dark Poison · 06-23-2009, 07:39 AM

Works like a charm. Thanks for your swift and accurate reply!

ShosMeister · 06-23-2009, 08:05 AM

Glad it worked. Just made me miss my brown beer and King Ribs. Oh well, maybe I'll get a chance to visit some day.

Cheers

06-23-2009, 06:29 AM	#1 (permalink)
Dark Poison Registered User Join Date: Dec 2007 Posts: 13 OS: Win XP Home	Excel formula to match a number from a selection of data (OR and IF function) Hi there, i'm having difficulty trying to make this forumla behave itself. Basically i want a set price to be given to each station if it falls under that particular price category. The formula is as follows; =IF(OR(I4="Hatfield", I4="Huntingdon", I4="Letchworth"),90,(IF(OR(I4="Bedford", I4="Flitwick", I4="Stevenage"),125,(IF(OR(I4="Downham Market", I4="Kings Lynn"),70,(IF(OR(I4="Luton", I4="LAP", I4="Luton Airport Parkway", I4="St Albans", I4="St Albans & Harpenden"),140),(IF(I4="Hitchin",100)))))))) now when i fill series this down my list, it works for all stations exept for Hitchin, Luton, Luton airport Parkway, St Albans & Harpenden, Letchworth and St Albans. Why is that? I4 is the cell and column with the name of each station, of course.

06-23-2009, 07:23 AM	#2 (permalink)
ShosMeister Registered User Join Date: Jan 2008 Location: Harpers Ferry, WV Posts: 400 OS: OS X 10.4.x	Re: Excel formula to match a number from a selection of data (OR and IF function) You had an extra ) in there after your 140 before the last IF. Aligning the (...) helped to find it. I also took out the extra ( that you have before all the IFs. Code: =IF( OR(I4="Hatfield", I4="Huntingdon", I4="Letchworth"), 90, IF( OR(I4="Bedford", I4="Flitwick", I4="Stevenage"), 125, IF( OR(I4="Downham Market", I4="Kings Lynn"), 70, IF( OR(I4="Luton", I4="LAP", I4="Luton Airport Parkway", I4="St Albans", I4="St Albans & Harpenden"), 140, IF( I4="Hitchin", 100, "INVALID VALUE" ) ) ) ) )

06-23-2009, 07:39 AM	#3 (permalink)
Dark Poison Registered User Join Date: Dec 2007 Posts: 13 OS: Win XP Home	Re: Excel formula to match a number from a selection of data (OR and IF function) Works like a charm. Thanks for your swift and accurate reply!

06-23-2009, 08:05 AM	#4 (permalink)
ShosMeister Registered User Join Date: Jan 2008 Location: Harpers Ferry, WV Posts: 400 OS: OS X 10.4.x	Re: Excel formula to match a number from a selection of data (OR and IF function) Glad it worked. Just made me miss my brown beer and King Ribs. Oh well, maybe I'll get a chance to visit some day. Cheers