Calculate In-Between Numbers

1. Calculate In-Between Numbers

User wants to combine within one formula:
If C8 < x,then C6-90
If C8 > x but under xx, C6-120
If C8 > xx but < xxx, C6-150
If C8 > xxx, C6-180

Is there a function that will do the in-between parts for me?

2. RE: Calculate In-Between Numbers

=IF(C8<A1,C6-90,IF(C8<A2,C6-120,IF(C8<A3,C6-150,C6-180)))

a1 = x
a2 = xx
a3 = xxx

"Brenda Rueter" wrote:

> User wants to combine within one formula:
> If C8 < x,then C6-90
> If C8 > x but under xx, C6-120
> If C8 > xx but < xxx, C6-150
> If C8 > xxx, C6-180
>
> Is there a function that will do the in-between parts for me?
>
>
>

3. Re: Calculate In-Between Numbers

This does not take into account >x but < than xx.
Let's say
a1=100
a2=200
a3=300

we want smaller than 300 but larger than 200. That's the part we're having
trouble putting together. The straight nesting IF statement is no problem.

"LanceB" <LanceB@discussions.microsoft.com> wrote in message
news:EFCF8C15-90C1-4767-A48E-1F65B214D266@microsoft.com...
> =IF(C8<A1,C6-90,IF(C8<A2,C6-120,IF(C8<A3,C6-150,C6-180)))
>
> a1 = x
> a2 = xx
> a3 = xxx
>
> "Brenda Rueter" wrote:
>
> > User wants to combine within one formula:
> > If C8 < x,then C6-90
> > If C8 > x but under xx, C6-120
> > If C8 > xx but < xxx, C6-150
> > If C8 > xxx, C6-180
> >
> > Is there a function that will do the in-between parts for me?
> >
> >
> >

4. Re: Calculate In-Between Numbers

Brenda

One way:

=C6-(C8<x)*90-(AND(C8>=x,C8<xx))*120-(AND(C8>=xx,C8<xxx))*150-(C8>=xxx)*180

assuming >=x, >=xx and >=xxx

--
Best Regards
Leo Heuser

"Brenda Rueter" <bkrueteNOSPAM@michigan.gov> skrev i en meddelelse
news:eqqwPHWVFHA.3280@TK2MSFTNGP09.phx.gbl...
> User wants to combine within one formula:
> If C8 < x,then C6-90
> If C8 > x but under xx, C6-120
> If C8 > xx but < xxx, C6-150
> If C8 > xxx, C6-180
>
> Is there a function that will do the in-between parts for me?
>
>

5. Re: Calculate In-Between Numbers

Hi Brenda,

> User wants to combine within one formula:
> If C8 < x,then C6-90
> If C8 > x but under xx, C6-120
> If C8 > xx but < xxx, C6-150
> If C8 > xxx, C6-180
>
> Is there a function that will do the in-between parts for me?

lookup() will do the job for you, have a look in excel help. however,
make sure you get correct results if eg. c8=xx (exactly xx, not
smaller, not bigger).

you need a table "data" like this one
0 90
x 120
xx 150
xxx 180

then you can use
=c6-lookup(c8,data,2,TRUE)

arno

6. Re: Calculate In-Between Numbers

"Brenda Rueter" <bkrueteNOSPAM@michigan.gov> wrote in message
news:eqqwPHWVFHA.3280@TK2MSFTNGP09.phx.gbl...
> User wants to combine within one formula:
> If C8 < x,then C6-90
> If C8 > x but under xx, C6-120
> If C8 > xx but < xxx, C6-150
> If C8 > xxx, C6-180

Brenda, try this one:
=IF(C8>xxx,C6-180,IF(C8>xx,C6-150,IF(C8>x,C6-120,C6-90)))
Not elegant at all, a simple translation of your requirements.

7. Re: Calculate In-Between Numbers

ooops:

it should be VLOOKUP!

arno

> lookup() will do ...
> then you can use
> =c6-lookup(c8,data,2,TRUE)

8. Re: Calculate In-Between Numbers

Thanks everyone. I'm working with the different solutions offered here.

"Markus L" <uo9oew@lnubb.pbz> wrote in message
news:Gb3ge.3\$ae.3480@ns2.gip.net...
> "Brenda Rueter" <bkrueteNOSPAM@michigan.gov> wrote in message
> news:eqqwPHWVFHA.3280@TK2MSFTNGP09.phx.gbl...
> > User wants to combine within one formula:
> > If C8 < x,then C6-90
> > If C8 > x but under xx, C6-120
> > If C8 > xx but < xxx, C6-150
> > If C8 > xxx, C6-180

>
> Brenda, try this one:
> =IF(C8>xxx,C6-180,IF(C8>xx,C6-150,IF(C8>x,C6-120,C6-90)))
> Not elegant at all, a simple translation of your requirements.
>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

X vBulletin 4.1.8 Debug Information

• Page Generation 0.06137 seconds
• Memory Usage 8,962KB
• Queries Executed 16 (?)
Template Usage (30):
• (1)footer
• (1)forumrules
• (1)gobutton
• (8)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (8)postbit_legacy
• (8)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (40):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php
• ./includes/functions_prefix.php

Hooks Called (44):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_display_complete
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• memberaction_dropdown
• tag_fetchbit_complete
• forumrules