Formula Depending on a value

[likeatiger81]

Hi All,

i have a sheet which is being used as a payment advise with subsidies.
depending on the product selected, it is either a $value multiplied by the qty or a % of the total value.

what i am trying to do as an example:

if cell F46 = percent(or sheet2 b6) it needs to multiply by cell H32

BUT if cell F46 = $ (or sheet2 B7:B11) it needs to multiply by cell B46

(sheet 2 has named ranges to be used in a list box)

Any help on the matter would be greatly appreciated.

Cheers

 

[MDCurry]

There are 2 ways you can approach this. The preferred approach depends on how you define "percent" vs "value". If "percent" is defined as any number between 0 and 1 then you could use an IF formula like:

=IF(F46>0,IF(F46<=1,F46*H32,F46*B46),F46*B46)

If however "percent" can be any number so long as it is formatted as a percent...like say 300%...which is really just a value of 3 then you'd have to use something like.

=IF(LEFT(CELL("format",F46),1)="P",F46*H32,F46*B46)

Hit F9 key to force calculation if you change the format of F46.

 

[likeatiger81]

thank you i tried the formula

however F46 is still multiplying by B46 regradless of what is in F46

i pushed F9 to force the calculation, and i can see in the formula it is using H32 in the formula, but the end result still seems to be F46*B46 weather it is a % or $

any ideas???

Thanks again

 

[lenze]

Hi and welcome to the board!!!
Can you explain a little bit more??
Is the formatting of F46 being changed, thus determining the Value of F46? If so, how??
Would code work for you??

lenze

 

[MDCurry]

Which formula are you using?

If you are using:
=IF(F46>0,IF(F46<=1,F46*H32,F46*B46),F46*B46)
...this should work if you change the value in F46 from say 15 to .25.

If you are using format dependent formula:
=IF(LEFT(CELL("format",F46),1)="P",F46*H32,F46*B46)
...this will calculate based on how you have formatted cell F46. That is if you format as a percent (hit F9) then multiply by H32. If you format as something other than a percent (hit F9) then it will multiply by B46.

What values are you testing in cell F46?

 

[likeatiger81]

hi thanks for a speedy reply

cell F46 changes based on what information is put into C46 which uses =VLOOKUP(C46,LEVIE,2,FALSE) to automatically put in the amount of the levie depending on the product selected.

to give you an example

if i have lambs sold that are under $75 p/head they get a 2% levie on the total invoice amount (H32)

if i have lambs sold that are over $75 p/head they get a levie of $1.50*by the number of head being sold (B46)

so what i am trying to do is to get the total using the formula (in english)
if cell F46 is a percent i need to multiply F46*H32 , BUT if cell F46 is a $ value i need to multiply F46*B46

i hope i didnt confuse you, its the plainest way to explain it

cheers

 

[lenze]

Are those the only 2 options for C46, or there many more??

 

[likeatiger81]

MDCurry

thank you i think we had it the first time round using
=IF(F46>0,IF(F46<=1,F46*H32,F46*B46),F46*B46)
but i think my cells in sheet 2 were formated incorrectly

it seems to be working at the moment

thanks alot you have saved me one big headache!!!

 

[likeatiger81]

ok so it turns out i have one more problem

the cells with no value return the #N/A

therefore the totals are not working

the formula in one cell is
=IF(F49>0,IF(F49<=1,F49*H35,F49*B49),F49*B49)

the subtotal formula is
=SUM(H46:H51)

the total formula is
=SUM(H40+H41-H53)

how do i add into these formula's to ignore the #N/A cells?

Thanks

 

[MDCurry]

Change your formula...

=IF(F49>0,IF(F49<=1,F49*H35,F49*B49),F49*B49)

..to:

=IF(ISERROR(IF(F49>0,IF(F49<=1,F49*H35,F49*B49),F49*B49)),"",IF(F49>0,IF(F49<=1,F49*H35,F49*B49),F49*B49))

..and copy where appropriate.

This will return null if any error found. If you'd rather return a 0 then substitute 0 for "" in above formula.