Calculate ROI - Formula Not Working

[sprigelz]

Good day,

I have the below formula which looks for the Net Income / Expenses to calculate an ROI between 2 given dates. I don't believe it is calculating properly. Could you please assist.

=iferror(index(F$4:F$15,match(I20,B$4:B$15,0))/index(F$20:F$31,match(I20,B$20:B$31,0)),0)

Current output for 1 month;
Net Income: 45.86
Expenses: 283.36
Formula result: 16.18%

If I'm not mistaken, this should be resulting in a negative number, should it not?

 

[steve the fish]

A machine cant know that income is positive and expenses are negative unless you present them as positive and negative numbers. We cant see the data to tell. As for your question no 45.86/283.36 should not be negative.

 

[etaf]

what does each formula return separately ?
index(F$4:F$15,match(I20,B$4:B$15,0))
index(F$20:F$31,match(I20,B$20:B$31,0))

Do you get the expected results ?

otherwise - perhaps
Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[sprigelz]

what does each formula return separately ?
index(F$4:F$15,match(I20,B$4:B$15,0))
index(F$20:F$31,match(I20,B$20:B$31,0))

index(F$4:F$15,match(I20,B$4:B$15,0)) = 45.85
index(F$20:F$31,match(I20,B$20:B$31,0)) = 283.36

Net Income: 45.86
Expenses: 283.36

The Index Match results are pulling the exact numbers that I need them to. I just need to figure out how to transform these 2 numbers into ROI.

Based on @steve the fish response, I changed the formula to the below. Though I still feel that this result is incorrect, it should be much higher, right?

=iferror(index(F$4:F$15,match(I29,B$4:B$15,0))/(index(F$20:F$31,match(I29,B$20:B$31,0))*-1),0)

 

[igold]

Where did you get your formula from... Clearly 45.86 is 16.18% of 283.36. However what is the amount that of your total investment amount that threw off your income and expenses. You can only figure an ROI with a total investment. All I see are income and expenses.

 

[steve the fish]

Normally it would be profit / cost. Assuming no other variables you would want (income - expenses) / expenses which indeed will be negative.

 

[sprigelz]

@steve the fish Thank you very much. That was exactly what I needed.

 

[igold]

Out of curiousity, are you in a Retail situation where you are trying caluculate Gross Margin %...

 

[sprigelz]

@igold My apologies for using the incorrect terminology. I appreciate all the assistance received here today!

 

[steve the fish]

As a FYI this is semantics and as far as im concerned you can calc a ROI on an expense if you so wish.