# Need help with function in Excel...

Discussion in 'OT Technology' started by DatacomGuy, Apr 14, 2006.

1. ### DatacomGuyis moving to Canada

Joined:
Oct 14, 2002
Messages:
16,546
0
Location:
Tampa, FL
I want to create a function in a quote form..

In column C15 I have a qty. In column D15, I have a unit of measure (EA for each, C for hundred, M for thousand). In K15, I have sell price, and M15, I have extended price.

In M15, I want to make a statement that says something to the effect of "If D15 = EA, then K15*C15=X/1", and then same for C and M unit of measures.

Anyone know what formula would be proper for this?

I tried =IF(D15=EA,"K15*C15=/1", but didn't work. I'm missing something here.

Anyone?

2. ### Zak8022Active Member

Joined:
Apr 16, 2004
Messages:
4,012
0
Location:
Maryland
The reason you function is failing is because of what i bolded and underlined.

I'm not sure i quite understand all of the problem.... or i dont have enough info to help fully.

Can you please explain what you are trying to do with the "=/1" part.

3. ### Zak8022Active Member

Joined:
Apr 16, 2004
Messages:
4,012
0
Location:
Maryland
On second thought... Why is there a need to have different math performed if you have a seperate price column? You could simply do
Code:
`=K15*C15`
Unless i am not understanding the real meanings of Price and Extended Price.

4. ### DatacomGuyis moving to Canada

Joined:
Oct 14, 2002
Messages:
16,546
0
Location:
Tampa, FL
I'll explain.

Unit of measure is [SIZE=-1]a standard of basic quantity or increment by which something is divided, counted, or described.

In this case, "EA" would mean that the extended price would be divided by 1. "C" would mean that the extended price would be divided by 100. "M" would mean that the extended price would be divided by 1000.

Examples.

Line 1 of the quote could have 5000FT of fiber. This is priced per ft, so unit of measure would be "EA", which would mean that the extended price should be divided by 1. Say the selll price is 1.00/ft, so the extended price would be 5000.00.

Line 2 of the quote could have 5000FT of Category 6 cable. This is priced per thousand feet, so unit of measure would be "M", which would mean that the extended price should be divided by 1000. Say the sell price is 100.00/m, the extended should read 500.00.

And same would go for "C", but divisible by 100.

I want to create a formula or function, that will recognize what is being inputted into the UOM column, and change the formula in the extended column to reflex the UOM.

Let me know if I've confused this further.. I can post my quote form if necessary, although I'd prefer not to.
[/SIZE]

5. ### Zak8022Active Member

Joined:
Apr 16, 2004
Messages:
4,012
0
Location:
Maryland
Ok... i think i get it... lemme work on it a little before i go asking for your form. That may not be necessary.

6. ### Joe_CoolModerator

Joined:
Jun 30, 2003
Messages:
311,541
5,514
Try putting this in M15:

=if(D15="C",C15*K15/100,if(D15="M",C15*K15/1000,C15*K15))

That way, if D15 is C, you'll get quantity * price /100.
If not, it triggers the nested if.
If D15 is M, you get quantity * price /1000.
Otherwise, you get the default (EA), which is quantity * price.

7. ### Joe_CoolModerator

Joined:
Jun 30, 2003
Messages:
311,541
5,514
And you can extend that for other values of D15 by replacing "C15*K15" with other nested if statements, and leave "C15*K15" for the default value (the else in your innermost if statement).

8. ### Zak8022Active Member

Joined:
Apr 16, 2004
Messages:
4,012
0
Location:
Maryland
Ok... try this:

Code:
`=IF(D15="EA",(K15*C15)/1,IF(D15="C",(K15*C15)/1000,IF(D15="M",(K15*C15)/100)))`
I tried it... and i think it works the way you want. Get back to me and let me know.

9. ### Zak8022Active Member

Joined:
Apr 16, 2004
Messages:
4,012
0
Location:
Maryland
Damn, Joe_Cool beat me to it.... but i think we came up with the same things.

edit: we're actually exactly the same... he just didnt write in the part of the function for EA. And i think i switched around the C/M divisors.

10. ### Joe_CoolModerator

Joined:
Jun 30, 2003
Messages:
311,541
5,514
Yeah, I left out EA and just left /1 as the default value, as a catch-all for invalid entries in that column.

Joined:
Oct 14, 2002
Messages:
16,546