turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 120K Members
- 2,632 Online
- 30.1K Conversations

- Home
- :
- Excel
- :
- Formulas and Functions
- :
- Nested IF

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 02:53 PM

Hello,

I need a little nudge on a formula.

I am working on a spreadsheet that calculates day rates for my business.

Let's say a day is ten hours and the rate for that day is 1000 dollars. Each day requires the transport and setup of multiple pieces of equipment. Since that equipment needs to be set up and taken down each day, the minimum fee for a day is 650 dollars or .65.

We quote in 1/2 or full days. I have an IF statement that will retrieve 650 dollars when it sees 0.5 in the referenced column. What I want is for it to apply that same logic when it sees any variant of .5, like 1.5 or 2.5, etc. So if the number of days in column A is 1.5, I want it to return the value 1650 dollars.

This spreadsheet generates the actual quote and I don't want to have use increments other than .5 for the calculation.

Make sense?

Labels:

12 Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 04:09 PM - edited 02-06-2018 04:23 PM

=(INT(quote)+MAX((MOD(quote,1)>0)*0.65, MOD(quote,1)))*rate

quote can be 1,1.5, 1.7, 2, 2.1, etc.

rate should be 1000, or the absolute cell reference of the cell contains the rate

if only those quote > x.5 and < x.65 turn to x.65

=(INT(quote)+MAX((MOD(quote,1)>=0.5)*0.65, MOD(quote,1)))*rate

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 04:11 PM

Hi Randy

Are you able to upload a sample of the spreadsheet? Might be good to see what you are working with!

Cheers

Damien

Are you able to upload a sample of the spreadsheet? Might be good to see what you are working with!

Cheers

Damien

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 07:51 PM

Willy,

I'm trying to get my head around this. I got it to partially work but it seems to be working opposite.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Best Response confirmed by Randy Taillon (Occasional Contributor)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 09:06 PM - edited 02-06-2018 09:07 PM

In addition to Willy's answer, see if this formula works for you.

=IF(MOD(B3,1),SUM(INT(B3)*1000,650),B3*1000)

Basically, I've devised an IF statement that checks to see if the number is a whole number or decimal.

If it is a **whole number**, then do **B#** **x 1000**.

If it is a **decimal value**, then **B# x 1000 + 650**.

I have also modified your spreadsheet and have attached it with the formula in action.

The formula is based on the assumption that you will use only 0.5 increments as you have stated in your post (therefore anything like .1 to 0.9 will always read as $650).

I hopefully have understood you right with what you want but if not, Willy and I can try again!

Cheers

Damien

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 09:13 PM

Great job Willy!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 09:17 PM

Willy,

Perfect solution!

Works exactly as I wanted it to and preserves the ability to use decimals other than .5.

Thank you!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 09:31 PM

Thanks, Damien. It is because you asked the sample. If not, I wouldn't know about column C & D.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 09:34 PM

I like getting a visual when people have complex requirements, glad it helped with the solution.

Love your work Willy, you are awesome at Excel. I learned something new from you!

Cheers

Damien

Love your work Willy, you are awesome at Excel. I learned something new from you!

Cheers

Damien

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 09:35 PM

Damien,

You understood me perfectly.

While it's unlikely I'll use a decimal other than .5, I would prefer to preserve the ability to do so. I did not however state that in previous posts.

I think I get it now. The magic lies in INT. Basically, the formula says if it's a whole number, multiply by 1000 (or F3) and if it contains a decimal, substitute .65 for whatever the decimal is.

I'm glad I got two different responses. That gave me a couple ways to look at it and made it click for. I will use both versions. Yours I will use to distribute to staff that don't have the authority to change pricing and Willy's I will use in my version.

Thanks! I sincerely appreciate both of you being willing to help out.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 09:38 PM

Thanks for the kind words Randy. I think we (in the tech community) just have an awesome amount of fun helping good people like yourself problem solve things.

We're always here if you need anything!

Best wishes

Damien

We're always here if you need anything!

Best wishes

Damien

Related Conversations

Add 5 if a cell contains text "yes"

Titchard Family
in
Formulas and Functions
on
11-30-2017
174
Views

0 Likes

6 Replies

IFERROR - HLOOKUP- IF formula error

data24365
in
Formulas and Functions
on
01-12-2018
185
Views

0 Likes

14 Replies

I have a nesting formula I am trying to restrict the out put by todays date -7

samuel afroman
in
Formulas and Functions
on
02-15-2018
39
Views

0 Likes

1 Replies

evaluating value in previous record of same field

brett.spector null
in
Get and Transform Data
on
02-09-2018
31
Views

0 Likes

1 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft