Excel formula

  Savannah 22:31 01 Apr 2003
Locked

I have 1 page with a different exchange rate for each month of the year.
On the other sheet I have 1 column with the date and another with an amount of cash.
I would like to make an automatic formula that says if the date = Jan etc then calculate the amonut x the exchange rate. I have found the excel only allows 7 if rules.
Anybody got a solution

  tbh72 22:36 01 Apr 2003

Ummmm can you create a named table in a seperate area of your sheet, then use a hlookup to get the correct exchange rate for your calculation.

Just a suggestion?

  rasalom 00:57 02 Apr 2003

if you use the name box to name all the different exchange rates (eg January's exchange rate, etc) on page 1 then you can get round the 7 nested IF statesments rule by referencing the named areas (and not the months cell references) in your IF statement on page 2.

Hope this makes sense.

  cherria 09:32 02 Apr 2003

on page 1 (lets assume its called 'Sheet1') assuming you have the month in column A and the rate in column B and that you have column titles in row 1. Rather than putting just 'January' in column A, put 1-jan-2003 and instead of 'February' put 1-feb-2003 etc. then format this using a custom format as mmmm.

What you then get is just the month displayed but the value in the cell is actually a date representing the 1st of each month.

Next, on sheet 2 assuming you have column titles in row 1 and that month is in column A and the amount of cash in column B, put this formula in cell C2

=VLOOKUP(a2,sheet1!$A$2:$B$13,2,TRUE)

this assumes there are 12 values, 1 per month?

copy this formula down for every cash value you have and that should do the trick.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

iPhone X news: Release date, price, new features & specs

Black Friday 2017 : date, sites participants & bonnes affaires